Microsoft Office Tutorials and References

In Depth Information

Excel's lookup functions treat empty cells in the result range as zeros. The worksheet in the accompanying figure

contains a two-column lookup table, and the following formula looks up the name in cell B1 and returns the cor-

responding amount:

=VLOOKUP(B1,D2:E8,2)

Note that the Amount cell for Charlie is blank, but the formula returns a 0.

If you need to distinguish zeros from blank cells, you must modify the lookup formula by adding an IF function to

check whether the length of the returned value is 0. When the looked up value is blank, the length of the return

value is 0. In all other cases, the length of the returned value is nonzero. The following formula displays an empty

string (a blank) whenever the length of the looked-up value is zero, and the actual value whenever the length is

anything but zero:

=IF(LEN(VLOOKUP(B1,D2:E8,2))=0,””,(VLOOKUP(B1,D2:E8,2)))

Alternatively, you can specifically check for an empty string, as in the following formula:

=IF(VLOOKUP(B1,D2:E8,2)=””,””,(VLOOKUP(B1,D2:E8,2)))

To understand how this formula works, start with the MATCH function. This function searches the range

D2:D21 for the date in cell B1. It returns the relative row number where the date is found. This value is then

used as the second argument for the INDEX function. The result is the corresponding value in F2:F21.

Specialized Lookup Formulas

You can use some additional types of lookup formulas to perform more specialized lookups. For instance, you

can look up an exact value, search in another column besides the first in a lookup table, perform a case-sensitive

lookup, return a value from among multiple lookup tables, and perform other specialized and complex lookups.

The examples in this section are available at this book's website. The filename is spe-

cialized lookup examples.xlsx.