Microsoft Office Tutorials and References

In Depth Information

**Functions Relevant to Lookups**

Figure 8-3 shows the tax rate example with a horizontal lookup table (in the range E1:J3). The

formula in cell B3 is

=HLOOKUP(B2,E1:J3,3)

Figure 8-3:
Using HLOOKUP to look up a tax rate.

The LOOKUP function

The LOOKUP function has the following syntax:

LOOKUP(lookup_value,lookup_vector,result_vector)

The function’s arguments are as follows:

h
lookup_value:
The value that you want to look up in the
lookup_vector.

h
lookup_vector:
A single-column or single-row range that contains the values to be

looked up. These values must be in ascending order.

h
result_vector:
The single-column or single-row range that contains the values to be

returned. It must be the same size as the
lookup_vector.

The LOOKUP function looks in a one-row or one-column range
(lookup_vector)
for a value

(lookup_value)
and returns a value from the same position in a second one-row or one-column

range
(result_vector).

Values in the
lookup_vector
must be in ascending order. If
lookup_value
is smaller than

the smallest value in
lookup_vector,
LOOKUP returns #N/A.

The Help system also lists an “array” syntax for the LOOKUP function. This alternative

syntax is included for compatibility with other spreadsheet products. In general, you

can use the VLOOKUP or HLOOKUP functions rather than the array syntax.

Figure 8-4 shows the tax table again. This time, the formula in cell B3 uses the LOOKUP function

to return the corresponding tax rate. The formula in B3 is