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




Custom Search