Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

When a blank is not a zero

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 corresponding 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)))

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 on the companion CD-ROM. The filename is

specialized lookup examples.xlsx
.

Looking up an exact value

As demonstrated in the previous examples, VLOOKUP and HLOOKUP don’t necessarily require an

exact match between the value to be looked up and the values in the lookup table. An example

of an approximate match is looking up a tax rate in a tax table. In some cases, you may require a