Microsoft Office Tutorials and References

In Depth Information

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 later. For compatibility with previous versions, use the follow-

ing formula:

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

VLOOKUP(B1,EmpList,2,FALSE))

Looking up a value to the left

The VLOOKUP function always looks up a value in the first column of the lookup range. But what if you want

to look up a value in a column other than the first column? It would be helpful if you could supply a negative

value for the third argument for VLOOKUP — but you can't.

Figure 8-7 illustrates the problem. Suppose you want to look up the batting average (column B, in a range

named
Averages
) of a player in column C (in a range named
Players
). The player you want data for appears in a

cell named
LookupValue.
The VLOOKUP function won't work because the data is not arranged correctly. One

option is to rearrange your data, but sometimes that's not possible.

Figure 8-7:
The VLOOKUP function can't look up a value in column B, based on a value in column C.