Microsoft Office Tutorials and References

In Depth Information

If the lookup_value argument is text (and the fourth argument, range_lookup, is

FALSE), you can include the wildcard characters * and ?. An asterisk matches any

group of characters, and a question mark matches any single character.

The classic example of a lookup formula involves an income tax rate schedule (see Figure 8-2). 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 in cell B2:

=VLOOKUP(B2,D2:F7,3)

Figure 8-2:
Using VLOOKUP to look up a tax rate.

The lookup table resides in a range that consists of three columns (D2:F7). Because the third argument for the

VLOOKUP function is 3, the formula returns the corresponding value in the third column of the lookup table.

Note that an exact match is not required. If an exact match is not found in the first column of the lookup table,

the VLOOKUP function uses the next largest value that is less than the lookup value. In other words, the func-

tion uses the row in which the value you want to look up is greater than or equal to the row value, but less than

the value in the next row. In the case of a tax table, this is exactly what you want to happen.

The HLOOKUP function

The HLOOKUP function works just like the VLOOKUP function except that the lookup table is arranged hori-

zontally instead of vertically. The HLOOKUP function looks up the value in the first row of the lookup table

and returns the corresponding value in a specified table row.

The syntax for the HLOOKUP function is

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)