Microsoft Office Tutorials and References

In Depth Information

**Tip 34: Looking Up an Exact Value**

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 isn’t found, the formula returns #N/A. This is exactly what you want

to happen, of course, because returning an approximate match for an employee number makes no

sense. Also, notice that the employee numbers in column D aren’t in ascending order. If the last

argument for VLOOKUP is FALSE, the values don’t need to be in ascending order.

Figure 34-2:
This lookup table requires an exact match.

If you prefer to see something other than #N/A when the employee number isn’t found, you can use

the IFERROR function to test for the #N/A result (using the ISNA function) and substitute a different

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

=IFERROR(VLOOKUP(B2,D1:E11,2,FALSE),”Not Found”)

The IFERROR function was introduced in Excel 2007, so if your workbook must be compatible with

Excel 2003 and earlier versions, use this formula:

=IF(ISERROR(VLOOKUP(B2,D1:E11,2,FALSE)),”Not Found”,

VLOOKUP(B2,D1:E11,2,FALSE))