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
 
Search JabSto ::




Custom Search