Microsoft Office Tutorials and References
In Depth Information
Looking Up an Exact Value
Looking Up an Exact Value
The VLOOKUP and HLOOKUP functions are useful if you need to return a value from a table (in a
range) by looking up another value.
The classic example of a lookup formula involves an income tax rate schedule (see Figure 120-1).
The tax rate schedule shows the income tax rates for various income levels. The following
formula (in cell B3) returns the tax rate for the income value in cell B2:
=VLOOKUP(B2,D2:F7,3)
Figure 120-1: Using VLOOKUP to look up a tax rate.
The tax table example demonstrates that VLOOKUP and HLOOKUP don’t require an exact match
between the value to be looked up and the values in the lookup table. In some cases, though, you
might require a perfect match. For example, when looking up an employee number, close doesn’t
count. You require a perfect match for the number.
To look up only an exact value, use the VLOOKUP (or HLOOKUP) function with the optional
fourth argument set to FALSE.
Figure 120-2 shows a worksheet with a lookup table that contains employee numbers (column D)
and employee names (column E). The formula in cell B2, which follows, looks up the employee
number entered in cell B1 and returns the corresponding employee name:
=VLOOKUP(B1,D1:E11,2,FALSE)
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.

Search JabSto ::

Custom Search