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
Figure 8-3: Using HLOOKUP to look up a tax rate.
The LOOKUP function
The LOOKUP function has the following syntax:
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
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