Excel 2010 Bible

Part II: Working with Formulas and Functions

318

Specialized Lookup Formulas ...........................................................................................

You can use additional types of lookup formulas to perform more specialized lookups. For exam-

ple, 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.

On the CD
The examples in this section are available on the companion CD-ROM. The file is named 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 is

looking up a tax rate in a tax table. In some cases, you may require a perfect match. For example,

when looking up an employee number, you would require a perfect match for the number.

To look up an exact value only, use the VLOOKUP (or HLOOKUP) function with the optional fourth

argument set to FALSE.

Figure 14.6 shows a worksheet with a lookup table that contains employee numbers (column C)

and employee names (column D). The lookup table is named EmpList. The formula in cell B2,

which follows, looks up the employee number entered in cell B1 and returns the corresponding

employee name:

=VLOOKUP(B1,EmpList,2,FALSE)

Because the last argument for the VLOOKUP function is FALSE, the function returns a value only if

an exact match is found. If the value is not found, the formula returns #N/A. This result, of course,

continued

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 non-zero. The fol-

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