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

MATCH(lookup_value,lookup_array,match_type)

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

INDEX(array,row_num,column_num)

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