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

MATCH is

MATCH(lookup_value,lookup_array,match_type)

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

onerow range.

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.