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

Custom Search