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.