Microsoft Office Tutorials and References

In Depth Information

**Functions Relevant to Lookups**

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 function 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 horizontally 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)

The HLOOKUP functionâ€™s arguments are as follows:

h
lookup_value:
The value that you want to look up in the first row of the lookup table.

h
table_array:
The range that contains the lookup table.

h
row_index_num:
The row number within the table from which the matching value is

returned.

h
range_lookup:
Optional. If TRUE or omitted, an approximate match is returned. (If an

exact match is not found, the next largest value less than
lookup_value
is used.) If FALSE,

VLOOKUP searches for an exact match. If VLOOKUP cannot find an exact match, the

function returns #N/A.

If the
lookup_value
argument is text (and the fourth argument is FALSE), you can use

the wildcard characters * and ?. An asterisk matches any number of characters, and a

question mark matches a single character.