Microsoft Office Tutorials and References
In Depth Information
If the values in the first column are not arranged in ascending order, the LOOKUP func-
tion 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 reas-
on 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 MATCH is
The MATCH function's arguments are as follows:
• 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 ?.
• lookup_array: The range that you want to search. This should be a one-column or one-row range.
• 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 or-
der). If you omit the match_type argument, this argument is assumed to be 1.
The INDEX function returns a cell from a range. The syntax for the INDEX function is
The INDEX function's arguments are as follows:
• array: A range
• row_num: A row number within the array argument
• column_num: A column number within the array argument