Table of Contents
Summary
The INDEX MATCH formula finds a specific value within an array of data, based on a lookup value. The formula combines two Excel functions, INDEX and MATCH. This formula is most commonly used in place of the VLOOKUP function as it performs the same task but with more options and flexibility. For example, with INDEX MATCH the lookup array does not have to be the first column as it does with VLOOKUP.
Formula
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))
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 formula will return the value from the INDEX array that meets the condition defined by the MATCH function. 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 I6 looks for the order number in cell H6 (202) from the the lookup array B6:B16.
=INDEX(C6:C16, MATCH(H6, B6:B16, 0))
In this example the MATCH function will return the value “3” (for the third row) to the INDEX function. The INDEX function then returns the value “Andrews” as the overall result.
