Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

perfect match. For example, when looking up an employee number, you would probably 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 8-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)

Figure 8-6:
This lookup table requires an exact match.

Because the last argument for the VLOOKUP function is FALSE, the function returns an employee

name only if an exact match is found. If the employee number is not found, the formula returns

#N/A. This, of course, is exactly what you want to happen because returning an approximate

match for an employee number makes no sense. Also, notice that the employee numbers in

column C are not in ascending order. If the last argument for VLOOKUP is FALSE, the values need

not be in ascending order.

If you prefer to see something other than #N/A when the employee number is not

found, you can use the IFERROR function to test for the error result and substitute a

different string. The following formula displays the text Not Found rather than #N/A:

=IFERROR(VLOOKUP(B1,EmpList,2,FALSE),”Not Found”)

IFERROR works only with Excel 2007 and Excel 2010. For compatibility with previous

versions, use the following formula:

=IF(ISNA(VLOOKUP(B1,EmpList,2,FALSE)),”Not Found”,

VLOOKUP(B1,EmpList,2,FALSE))