Microsoft Office Tutorials and References
In Depth Information
Functions Relevant to Lookups
Figure 8-4: Using LOOKUP to look up a tax rate.
If the values in the first column are not arranged in ascending order, the LOOKUP
function may return an incorrect value.
Note that LOOKUP (as opposed to VLOOKUP) can return a value that’s in a different row than
the matched value. If your lookup_vector and your result_vector are not part of the same table,
LOOKUP can be a useful function. If, however, they are part of the same table, VLOOKUP is
usually a better choice if for no other reason than that LOOKUP will not work on unsorted data.
Combining the MATCH and INDEX functions
The MATCH and INDEX functions are often used together to perform lookups. The MATCH
function returns the relative position of a cell in a range that matches a specified value. The syntax for
The MATCH function’s arguments are as follows:
h lookup_value: The value that you want to match in lookup_array. If match_type is 0 and
the lookup_value is text, this argument can include the wildcard characters * and ?.
h lookup_array: The range that you want to search. This should be a one-column or
h match_type: An integer (–1, 0, or 1) that specifies how the match is determined.
If match_type is 1, MATCH finds the largest value less than or equal to lookup_value
lookup_array must be in ascending order). If match_type is 0, MATCH finds the first (
value exactly equal to lookup_value. If match_type is –1, MATCH finds the smallest
value greater than or equal to lookup_value lookup_array must be in descending (
order). If you omit the match_type argument, this argument is assumed to be 1.