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:

=VLOOKUP(B2,D2:F7,3)

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:

=VLOOKUP(B1,D1:E11,2,FALSE)