You can avoid the overhead of a double exact lookup if you use exact MATCH once, store
the result in a cell, and then test the result before doing an INDEX:

In A1 =MATCH(lookupvalue,lookuparray,0) In B1
=IF(ISNA(A1),0,INDEX(tablearray,A1,colnum))

When using a lookup over a large number of rows two approximate lookups are usually
faster than one exact lookup. The breakeven point is at about 10-20 rows. If you can sort
your data but still cannot use approximate match because you can't be sure that the value
you are looking up exists, then try this trick:

This does an approximate lookup on the lookup list, and if the lookup value equals the
answer in the lookup column you have found an exact match, therefore you want to redo the
approximate lookup on the column you want, otherwise it's a missing value. When using this
trick it's a good idea to add a dummy entry (usually zero or blank) to the list with a
value smaller than the smallest value you will ever lookup.

Often with lookups you want to get a number of columns and not just one.

If you are doing exact lookups on multiple columns you can save a lot of time using one
MATCH and many INDEX statements rather than many VLOOKUPs. Just add an extra column for
the MATCH to store the result (stored_row) and then for each column use:

INDEX(Lookup_Range,stored_row,column_number)

Alternatively you can use VLOOKUP in an array formula:

{VLOOKUP(lookupvalue,{4,2},FALSE)}

When using INDEX you can also return multiple columns or rows at once by using 0 as the
column or row number.

For example if you want to lookup a number of contiguous columns then use:

{INDEX($A$1:$J$1000,stored_row,0)}

This returns columns A to J in the stored row created by a previous MATCH.

Often you can choose between using VLOOKUP, INDEX and MATCH or OFFSET. VLOOKUP is
slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and
INDEX or OFFSET.

You can do Lookup using Wildcards: MATCH allows you to use the wildcard characters *
and ? when doing Exact Matches against text lists.

You can store the result of MATCH and reuse it in many INDEX or OFFSET functions.

You can do two stage lookups: If you have a table with two sorted indexes, such as
product within country, you can concatenate the country and product columns and Lookup
against that. This is slow. For large tables it can be much faster to use MATCH to find
the first and last row for the country you want to Lookup, and then do a Lookup for
product only within the rows for that Country.

INDEX has been improved from Excel 97 onwards and is now a non-volatile function
(speeds up recalculation) however OFFSET, which is also very fast, has not been changed
and is still volatile.

So the next time you find a slow-running spreadsheet check for Lookup bottlenecks, and
apply these tips and techniques to eliminate them!

Mapping With Excel
By Helen Bradley

By some twist of fate (or the application of some bizarre logic), the maps for Europe
and the World Countries were omitted from Office/Excel 2000. However, if you have your old
Office 97 discs, you can install these maps from this earlier version. Place your Office
97 or Excel 97 CD in the drive and locate the missing files in the \Os\Msapps\Datamap\Data
folder. While there's a long list of files you need set out in a <A
HREF="http://support.microsoft.com/directory/article.asp?id=KB;EN-US;q235303">Microsoft
Knowledge Base article Q235303</A>, in fact what you want are all the files with
filenames beginning with either E or W - there are 32 of these in total. Select them by
choosing Tools, Find, Files or Folders and search for "E*.*,W*.*" (without the
quote marks) then copy these to your C:\Program
Files\Common files\Microsoft Shared\Datamap\Data folder and the maps and their
features will now be available.

To see how the Map tool works, enter some data such as this in a sheet:

Select this data area, click the Map tool, and drag a rectangle for your map. If
more than one map matches your data, choose the one to use, in this case you'd use United
States (AK & HI Insert). If the Map tool can't match any of your geographic
names with it's own list, it offers alternate names for you to select from. Choose one of
these or choose to discard the data from your map.

By default, only one column (the first) of data is mapped. The states for which no data
exists are colored and the states for which there is data are shaded grey according to the
value of the data.

To use colors instead of shades of grey, drag the Category Shading button in the left of
the Microsoft Map Control dialog and drop it on the Sales 1999 button (in the bottom right
of the dialog) -- now color is used to represent various values as indicated in the
legend. There are other
plot formatting options you can use by dragging the button from the left of the dialog and
drop in onto the data button. If an option isn't available for that data, the data button
will be grayed out when you try to drop a formatting option onto it.

To replace the color for the unused states, right click the map and choose Features,
Custom and choose a color to use. To alter the colors used for the states for which data
is available, for example, where you've used Category Shading, right click the Legend and
click Edit, Category Shading Options tab.

The Zoom Percentage of Map drop down list on the Map toolbar allows you to change the size
of the map within the rectangle you've created for it. Use the Grabber tool to move the
map around in this area to get the best display and use the Select Objects tool to move
the Legend if it's overlapping the map.

Click the Map Labels option and choose Map Feature Names and click Ok. Now hold your mouse
over some of the states on the map and notice that their names appear. For states you've
plotted data you'll see your labels and for others the full state names. Click on a state
to add that state's name to the map. To add text to your map, select the Add Text
button, click where the text is to go, type it in the dialog and click Ok. There's also a
Map Features option which will add features like cities, highways and airports to your
map, right click the map and choose Features to see your options.

To plot two series of data on the map, drag the second series from the top of the Map
Control dialog and drop it over a Format/Column grey marker in the bottom right of the
dialog. This second series can be formatted using some other option than the one used for
the first, for example, use Value shading for one and Dot Density for the second. You
can't use the same format for both if you're using these types of formats.

However, you could plot two series on a Column chart. To do this, drag both series out of
the window to remove them (the bottom right of the dialog should now be empty). Drag the
Column Chart button over the Format marker then drag the first series and drop it to the
right of the Column
Chart button over the Column marker. Now drag the second series and drop it to the
immediate right of the first, over the Column marker, (don't use the one below it). Now
you'll have a Column chart for each state with two bars, one representing each series of
data.

If you change the figures in the underlying worksheet, make sure you refresh the map.
There were some problems with Excel 95 and 97 in that it didn't refresh when you selected
the Map Refresh button and I find Excel 2000 is, at best, unreliable at updating the map.
So, have a healthy disregard for this ability and always check any changes
carefully!

If the map doesn't automatically update when you click the Map Refresh button (or if the
Map Refresh button is disabled), click outside the map, right click it and choose Map
Object, Refresh Data. If you still have problems, try saving and closing the file and
reopen it and refresh the map if it isn't updated now. At worst, you may have to save and
close the file, exit Excel and load and open the Map again -- this was the workaround
suggested by Microsoft for versions 95 (v7).

Don't let difficulties with updating data put you off the Mapping tool. It's very handy
and pretty easy to use once you get familiar with it's interface and where to find
everything.

Note: Excel Energizer columnist Helen Bradley specializes in Office
applications and writing tips and hands-on tutorials. Her columns appear regularly in a
number of publications in the USA, Australia, UK and Canada.

DEALING WITH GROWING DATA (This instruction set
provided by Helen Bradley Excel Energizer columnist for Woody's Office Watch WOW)This week's Excel Energizer column was prompted by a reader query which went
something like this: "I am trying to make a spreadsheet to present the results from a
test. The workbook is called Test, column A contains a series of dates and column B
contains values for each of these dates. The number of rows is not known in advance
because new date/value pairs are added from time to time. My first problem is to find the
value of the cell in column A, for which the cell in column B has a maximum value".

In suggesting a solution, I'll assume that:

1. Row 1 contains headings and your data begins in row 2 2. The data is in Sheet1 of a
workbook 3. That the data in columns A and B represent data pairs and there are no entries
in column A for which there is no corresponding entry in column B, and vice versa. 4. In
the instance that there are two cells which contain the 'maximum' value, it is in order to
return the date for the first match

Based on these assumptions, here's a solution which returns the date from column A
which relates to the largest value in column B (or the first 'largest' value if this
figure appears more than once):

Begin by naming the data in column B. To do this, choose Insert, Name, Define and type
the name Data into the Names in workbook area, then in the Refers to area type:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

The CountA formula returns then number of filled cells in column B which is the number
of data cells plus one heading row. The range is, therefore, set to begin at cell B2 and
to end at the cell which is COUNTA rows below (less one row to account for the heading
row).

When this is done, you can add data to columns A and B and the range DATA will alter to
include those cells. This gives you a named range which you can use to refer to the
contents of column B without having to repeatedly calculate what cells in column B
actually contain data.

Now, to extract the cell in column A which relates to the highest value in column B,
use this formula:

=OFFSET(A1,MATCH(MAX(DATA),DATA,0),0)

MAX(DATA) returns the maximum value for the data in column B. The MATCH function
returns the position of this value in the DATA range, in this case the resulting number
will represent one row less than the actual row which contains the data, because of the
existence of the heading cell. The OFFSET function returns the value of a cell which has a
stated relationship to cell A1. In this case it is the MATCH number of rows down from A1
and in the same column.

You can place this formula in any cell in the worksheet - just ensure you format the
cell to a date format so you can read the resulting date.

The reader's second question is this: "Using the same data as described above,
make a chart, using the values in column A as the X-axis and the values in column B as the
Y-axis. Define the data source for the chart in such a way, that when more date/value
pairs are added to the sheet Test, the chart is automatically extended to encompass them -
without a need for manual editing of the data source of the chart to include the new
values."

The solution to this problem builds on the solution to the previous one. Begin by
repeating the process used above to name the cells in column A, in this case call the
range DATEDATA and name it using this Reference:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

Create a chart for your data as you would normally do. Then, with the chart selected,
choose Chart, Source Data. In the Values area type =sheet1!data and in the Category (X)
axis labels area type =sheet1!datedata and click OK.

The ranges which the chart is based on will now expand as new date/value data pairs are
added to the sheet.

The formulae for naming a range of cells so that the range will expand and contract
when data is added to or removed from it, and the method of altering a chart series to use
these ranges, are not my own but are based on solutions offered by John Walkenbach in his
excellent book Microsoft Excel 2000 Formulas. This book would make a welcome addition to
any Excel user's library.

Finally, reader Terry Farrell offers an alternative to the recent solution for placing
an Excel filename and path in Excel's title bar and which uses the little seen Office Web
Address button. Terry recommends you add this to your Menu Bar by right clicking the Menu
Bar and choosing Customize, Commands tab. From the Categories list choose Web then click
and drag the Address tool to the far right of your Menu Bar. Use your mouse to drag its
right hand border so it stretches to the full remaining width of the Menu Bar. When you do
this, the name and path to the current file is displayed clearly on the Menu Bar. Nice
idea Terry!

EXCEL WORKBOOK NAME IN TITLE BAR

Playing around in the Microsoft Knowledge Base recently, I found instructions for some
simple macros to add the workbook filename and path to the Excel title bar. The macros
themselves do little more than bring up the appropriate Save or Open dialog and then, when
you've finished with the dialog, the file's name and path are added to the title bar using
this piece of code:

There are a couple of things to consider and the first is that, in spite of there being
multiple options suggested for running the macros, there's only one which makes any real
sense. This is the suggestion to save them in a file that's opened automatically whenever
you open Excel and to assign the macros to your existing Save and Open toolbar buttons and
to your File menu's Open, Save, and Save As
options.

However, a word of warning. If you do alter your toolbar buttons and file menu options to
run the macros and, if you lose your personal.xls file, none of these buttons or menu
options will work anymore. In this case you'll need to remove the macro assignment from
the buttons by reversing
the process of assigning them. While the Knowledge Base article suggests you add the code
to just any file and save it in your XLStart folder, I prefer to use the Personal
Workbook, Personal.xls, which Excel will create for you if you don't have it already.
Check if you have personal.xls by choosing Window, Unhide. If it's there, unhide it and if
not, or if Unhide is grayed
out, then you can quickly get Excel to create the file for you.

Create your personal.xls file by choosing Tools, Macro, Record New Macro, type a name for
the macro (anything will do as it's just a way to create the file) and, from the Store
Macro In dropdown list choose Personal Macro Workbook and click Ok. Click in a cell on the
worksheet to select
it, which results in something being recorded, then choose Tools, Macro, Stop Recording.
Voila! you have a Personal.xls workbook so go ahead and unhide it using Windows, Unhide.
Follow the instructions in the Knowledge base article and add the code to a module in your
Personal.xls workbook.

Then Hide personal.xls again by activating it and choose Window, Hide. This makes the
macros available anytime Excel is opened, just remember to answer Yes to the prompt to
save Personal.xls when you exit Excel and it will be saved to your XLStart folder
automatically. Now you can assign
the macro code to your toolbar buttons and your File menu options.

You'll find some small limitations to the macros, for example, when you open a file from a
hyperlink in another workbook or when you open one from the list of last opened files on
the file menu the filename and path don't appear. However, in these instances, you can run
the macro by
simply hitting the Save button or add another custom toolbar button to run just the one
line of code I mentioned above which will have the same result.

PROTECT CELLS IN EXCEL

PROBLEM: In Microsoft Excel you can protect an individual sheet, but
how do you protect or unprotect individual cells?

SOLUTION: To protect a cell's contents, the sheet it lives on must be
protected: Tools, Protection, Protect Sheet; check the Contents box. By default all cells
in a sheet are protected (locked). So the trick is to first unprotect (unlock) just the
cells you want to be able to edit, then protect the sheet since by default all remaining
cells are locked. To unlock an individual cell/range select it, then select Format, Cells,
click the Protection tab, clear the Locked check box and click OK. Only unlocked cells can
be edited. By pressing the Tab key you can move from one unlocked cell to the next, making
data entry a snap and avoiding accidental edits to formulas.

Create a Macro that will work in all workbooks like
Word's Normal.dot
When you save a macro in Word, it is saved in Normal.dot, and is available for all Word
documents. On the other hand, when you save an Excel macro, it works only in that
worksheet. Right? Not necessarlily, there a way to make Excel macros available to all
worksheets.

Here is how: You can create a worksheet to hold all the macros and
then have that worksheet always run when you open Excel. To do this, run Windows Explorer
and go to c:\Program Files\Microsoft Office\Office\Xlstart (assuming you used the default
location when you installed Office). Right-click in the right pane of Xlstart and choose
New, Text Document. Name your new document Personal.xls and press Enter. When you're asked
if you want to change the extension, click Yes.

Now, double-click your new file to open it in Excel. Next, choose Window, Hide and then
choose File, Exit. When asked if you want to save the Personal macro workbook, click Yes.
The next time you run Excel, Personal.xls will run hidden.

To store your macros in Personal.xls, you will have to choose Window, Unhide to unhide
Personal.xls. After you finish storing your macros, choose Window, Hide to hide
Personal.xls again.

ERASING EXCEL CELL CONTENTS WITH THE MOUSE

When you have your hand on the mouse and don't want to reach over to press the Delete
key to erase the contents in Excel cells, simply select the cells that contain the data
you want to erase. Now, use the handle and drag backward over the cells. The cells will
turn gray as you do this. When you release the mouse button, the cell content is gone.
This operation is exactly the opposite of dragging the handle to AutoFill cells.

Some Excel users get into the habit of just pressing Space to clear a selected cell.
However, this is a habit you ought not get into, because some macros might have a problem
if they encounter a space in a cell.

The best way to clear a cell is to select the cell and choose Edit, Clear. When the
Clear submenu opens, choose All, Formats, Contents, or Comments.

If you need to clear only the contents, select the cell and press Delete.

USING AUTOCOMPLETE IN EXCEL WORKSHEETS

You can use AutoComplete in Excel worksheets to ease your load with data entry. For
example, if you type

Los Angeles

in one cell, then type

Los

in another cell, Excel completes the entry and places

Los Angeles

in the new cell.

If this doesn't work for you, you (or someone) may have turned off AutoComplete. To
turn on this feature, choose Tools, Options. When the Options dialog box opens, click the
Edit tab. Now select the check box labeled Enable AutoComplete For Cell Values and click
OK to close the dialog box and turn on AutoComplete.

USING FORMAT PAINTER IN EXCEL WORKSHEETS

We often talk about how to use the Format Painter in Word documents, but sometimes we
seem to forget that Excel has a Format Painter, too. This comes in very handy when you
need to use the same format at several locations in your worksheet.

To see how Format Painter works, open a blank worksheet and click cell A1. Type

Test

and press Enter. Move to cell C1, type

New Test

and press Enter.

Now click cell A1 again to select it. Next click the arrow at the right side of the
Font Color button (its icon is a bucket of paint). Select a new color from the color list.
Click the Format Painter button now, and then click cell C1. The data in C1 assumes the
color of the data in cell A1.

RANGE NAME RULES
What are the rules governing a range name in Excel?

The first character of a range name must be either an underscore character or a letter,
and the character count maximum is 255. Each character beyond the first can be either a
period, underscore, letter, or number. This means that spaces aren't allowed, but you can
separate words in a name with periods or underscores, like this:
"Profit_Per_Unit" or "Profit.Per.Unit". You can use mixed case, but
Excel doesn't differentiate names based on case, so if you have a range name
"Profit" and create a new one "PROFIT," the second name replaces the
first. A name can't be the same as a cell reference, so "A1" and
"R2D2" are invalid names. Bug alert: Although the Name Box displays range names
as long as 255 characters, if a name contains more than 253 characters, when you select it
nothing happens.

NCREASE YOUR EXCEL FLUENCY

What's the fastest way to create a named range in Excel?

First select the cell or range of cells you want to name. Then click on the "Name
Box" control. This control is located in the upper-left area of the worksheet display
(when you hover your mouse over it the tooltip reads "Name Box"). It is a combo
box control that sits above the grid's display of column headers (directly above the
"A" column). In a new worksheet it always reads "A1." Once you've
selected the Name Box, type the desired name and press Enter.

SLANTED COLUMN HEADINGS IN EXCEL

In the days when people made spreadsheets by hand, you would often see a slanted column
heading used to save space and enhance the spreadsheet's appearance. It's very easy to do
the same thing in an Excel worksheet. All you have to do is select the cell or block of
cells that contains the header text and choose Format, Cells. When the Format Cells dialog
box opens, click the Alignment tab. Now use the Degrees spin box to set the direction and
amount of slant you want for the headings. When you finish, click OK to close the dialog
box and apply your new text setting."

DRAGGING FROM EXCEL TO WORD

You need to drag a block of selected cells from an Excel worksheet to a Word document.
Follow these instructions, and you'll never accidentally deselect the block:

First select the block of cells in Excel. Now move the mouse pointer over an edge of
the selected block. When the pointer turns to a white arrow, press the left mouse button
and drag the selection to your Word document.

By the way, this method moves the cells to the Word document. If you want to COPY the
cells instead, move the mouse pointer over the edge; when it turns to a white arrow, press
Ctrl, then press the left mouse button to drag the selection to the Word document.

HIDE DATA IN EXCEL WORKSHEETS

Sometimes you may need to hide data in a particular cell in an Excel worksheet. Let's
say you have some data in cell C5 you would like to hide from the casual viewer. Click
cell C5 to select it, then choose Format, Cells. When the Format Cells dialog box opens,
click the Numbers tab (if necessary), then select Custom from the Category list. Now
double-click the Type entry box and type three semicolons:

;;;

Click OK to close the dialog box and accept your new formatting.

At this point, the data in cell C5 disappears. It's still there and will work in
calculations, but it isn't visible. If you need to check the data, just click the blank
cell and the contents appear in the Formula entry box.

SAVE MACROS TO RUN IN ALL EXCEL WORKSHEETS

When you save a macro in Word, it gets saved in Normal.dot and is available for all
Word documents. However, when you save an Excel macro, it only works in that worksheet. Is
there a way to make Excel macros available to all worksheets? Yes, there is.

You can create a worksheet to hold all the macros and have that worksheet always run
when you open Excel. To do this, run Windows Explorer and go to c:\Program Files\Microsoft
Office\Office\Xlstart (assuming you used the default location when you installed Office).

Right-click the right pane of Xlstart and choose New, Text Document. Name your new
document personal.xls and press Enter. When asked if you want to change the extension,
click Yes.

Now double-click your new file to open it in Excel. Next, choose Window, Hide and then
choose File, Exit. When asked if you want to save the Personal macro workbook, click Yes.
The next time you run Excel, personal.xls will run but remain hidden.

PROBLEM: You regularly create column headers in Microsoft Excel using
the days of the week, but you're tired of typing these in manually each time.

SOLUTION: First, open a new worksheet and type "Monday"
(without the quotes) in A1. Make sure A1 remains the active cell. Next, position your
mouse pointer on the cell's "fill handle" (the tiny black square in the lower
right corner of the active cell). The mouse pointer should turn into a black cross-hair
shape. Left-click and drag the fill handle one cell to the right-- don't release the mouse
yet--and you'll see a tooltip reveal that the next cell will be filled with
"Tuesday." You can drag over to the right as far as you need the day series to
run. The series fills in once you release the mouse.

ADD SOME FORMATTING
TO YOUR EXCEL PRINTOUTS

Excel doesn't print grid lines unless you ask it to do so. However, sometimes a
worksheet needs some lines to make it easier to read. In such a case, simply convert the
data area to a table.

All you have to do is select the data you want to format and choose Format, AutoFormat.
When the AutoFormat dialog box opens, select the type of formatting you want to apply,
then click OK to close the dialog box and apply your selection.

AutoFormat does a great job of sprucing up your worksheets. If you use a
black-and-white laser printer, you should select one of the simpler black-and-white
formats. Otherwise, your printout may look too dark and cluttered. Even if you have a
color printer, some of the more colorful formats can prove difficult to read.

ERASE EXCEL CELL CONTENTS WITH THE MOUSE

This tip is handy when you have your hand on the mouse and don't want to reach over to
press the Delete key to erase certain cell contents. Here's how it works:

Select the cells that contain the data you want to erase. Now use the handle to drag
backward over the cells. The cells turn gray as you do this. When you release the mouse
button, the cell contents disappear.

This operation is exactly the opposite of dragging the handle to AutoFill cells.

When you set an entire column to one decimal place, you can be sure that Excel will
always round the numbers consistently. Try this example.

Enter the following into cell A1:

23.55

Then choose Format, Cells, and set the decimal places to 1. Excel will display 23.6. If
you enter

23.54

Excel will display 23.5. Excel rounds UP if the digit is 5 or greater, and DOWN if the
digit is less than 5.

As far as calculations go, Excel retains its standard accuracy regardless of the number
of decimal places you choose to display. To check this out, enter the following numbers
respectively into cells A1 and A2:

23.456 20.237

Into cell A4, enter

=sum(a1:a2)

Now select A4 and choose Format, Cells. When the Format Cells dialog box opens, click
Number and enter three decimal places. Click OK to apply your settings and close the
dialog box. Cell A4 will display 43.703.

Select cells A1 through A2 and choose Format, Cells to set the decimal places to one.
Click OK, and cell A4 will still display 43.703 even though cells A1 and A2 now display
23.5 and 20.2.