Microsoft Office Tutorials and References

In Depth Information

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 of an approximate

match 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 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 D) and employee

names (column E). 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 D are not in ascending order. If the last argument for

VLOOKUP is FALSE, the values need not be in ascending order.