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-
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:
Alternatively, you can specifically check for an empty string, as in the following formula:
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.