Table of Contents
Summary
The INDEX MATCH MATCH formula finds a specific value within a two dimensional array of data, based on lookup values for the row and column. The formula combines two Excel functions, INDEX and MATCH. This is a powerful formula for finding information withing datasets using two lookup values–for the row and column.
Formula
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))
Note that the first MATCH function performs the lookup on the row, returning the value for the row to the INDEX function. The second MATCH function performs the lookup on the column, and returns the value for the column to the INDEX function. For additional details, see the INDEX function.
Arguments (inputs)
array = the array of data from which the result will be returned
lookup_value = the value to look for in the lookup_array
lookup_array = the array which contains the values to lookup
match_type = [optional] 1 = less than, 0 = exact match, -1 greater than
Return value
The INDEX MATCH MATCH formula will return the value from the INDEX array that meets the conditions defined by each of the MATCH functions. The formula will return the value for the first matching reference, even if there are multiple matches.
Example
In the example above the formula in cell N7 looks for the matching combination of sales representative, “Andrews” (L7), for the month of “May” (M7), within the array C7:H13.
=INDEX(C7:H13, MATCH(L7, B7:B13, 0), MATCH(M7, C6:H6, 0))
The first MATCH function looks up “Andrews” from the list of sales representatives and returns the relative position 3 to the INDEX function because Andrews is the third representative in the list.
The second MATCH function looks up “May” from the list of months and returns the relative position 5 to the INDEX function because May is the fifth month in the list.
Then the INDEX function looks up the value in the 3rd row and 5th column, and returns the value $7,843 from cell G9.
