Part II: Working with Formulas and Functions
316
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 isMATCH(lookup_value,lookup_array,match_type)The MATCH function’s arguments are as follows:l (^) lookup_value: The value you want to match in lookup_array. If match_type is 0 and the
lookup_value is text, this argument can include wildcard characters * and?
l (^) lookup_array: The range being searched.
l match_type: An integer (–1, 0 , or 1 ) that specifies how the match is determined.
Note
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 descend-
ing order.) 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:
l array: A range
l (^) row_num: A row number within array
l col_num: A column number within array
Note
If array contains only one row or column, the corresponding row_num or column_num argument is optional. n
Figure 14.5 shows a worksheet with dates, day names, and amounts in columns D, E, and F.
When you enter a date in cell B1, the following formula (in cell B2) searches the dates in column D
and returns the corresponding amount from column F. The formula in cell B2 is
=INDEX(F2:F21,MATCH(B1,D2:D21,0))
To understand how this formula works, start with the MATCH function. This function searches the
range D2:D21 for the date in cell B1. It returns the relative row number where the date is found.
This value is then used as the second argument for the INDEX function. The result is the corre-
sponding value in F2:F21.