Combination of Index & Match Function in Excel

Index & Match Function in Excel

The INDEX MATCH Formula is combination of two function in Excel: INDEX & MATCH.

INDEX() returns the value of a cell in a table based on the column and row number.

MATCH() returns the position of a cell in a row or column.

Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function.

The Index Function:

INDEX takes a cell range and returns a cell within that range based on a count provided by the user. The formula looks like this:

=INDEX(range, row_or_column)

In this example, the formula outputs "Salary", because the column  2  spaces into the specified range contains the text "salary".

In this example, the formula outputs 12000, because the row  2  spaces into the specified range contains the number 12000.

The MATCH function :

The MATCH function returns the position of a cell within an array by matching against a criteria string. The formula looks like this:

=MATCH(lookup_value, lookup_range, match_type)

In this example, the formula outputs the number 2, because the value "Shaym" is found 2 spaces into the specified lookup range.

In this example, the formula outputs the number 1, because the value "Employee Name" is found 1 spaces into the specified horizontal lookup range.

Then we combined these formulas together see the result is

Two Dimensional lookup with Index Match Function:

This example teaches you how to lookup a value in a two-dimensional range. I use the MATCH and INDEX , data validation and "&"  function. For Executive a formula containing "&" press CTRL+SHIF+ENTER.

First we execute the formula that match item in table

=match("item",range,0)

Second we execute the formula that match Name with Month in table

=match(name&month,range&range,0) Press ctrl+shift+enter

Second we execute the formula that is the core

=index(value range,2,3)

then replace 2,3 with our first and second formulas.