Microsoft Office Tutorials and References
In Depth Information
Tip 34: 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 34-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:
Figure 34-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 an exact 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 34-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: