The Excel INDEX MATCH function lets you perform advanced lookup tasks. For this reason, it is well known as a leading alternative to the VLOOKUP function. It is essentially a combination of the INDEX and MATCH functions.
INDEX MATCH Basics
Since it is a combination of two functions, let’s introduce each function separately.
Basic Definition of the INDEX Function
The INDEX function returns a specified value by entering its row and column positions within a specified data range.
The function syntax is as follows:
=INDEX (data range, row position, column position)
Basic INDEX Function Example
In the data below, the INDEX function returns the value in the third row and fourth column of the data range.
The formula used is as follows:
G3 cell =INDEX(B3:E13,3,4)
OUTPUT 1300
Basic Definition of the MATCH Function
The MATCH function is often used with the VLOOKUP function. This shows how versatile it is. Its basic definition is as follows.
It finds a criteria value within a specified data range and returns its position as a number.
Basic MATCH Function Example
In the data below, the MATCH function returns the row position of KIM as a number.
The formula used is as follows:
G6 cell =MATCH(B5,B3:B13,0)
OUTPUT 3
Basic INDEX MATCH Function Example
To use the Excel INDEX MATCH function, you must first understand the basics of INDEX and MATCH as explained above. However, that alone is not enough to use it to its full potential.
The function syntax is as follows:
=INDEX(data range, MATCH(row criteria, range, 0), MATCH(column criteria, range, 0)
Why Use the INDEX MATCH Function?
There are two reasons to use the Excel INDEX MATCH function.
- The first reason is that it lets you fully use Excel’s lookup capabilities.
- The second reason is that it lets you create a new data layout from the original data by changing rows and columns.
Using the INDEX MATCH Function
Let’s look at a practical use of the function. Data organized vertically by item has been rearranged by selecting the items to use as rows and columns.
The formula used is as follows:
H7 =INDEX($B$3:$E$13,MATCH(H$6,$D$3:$D$13,0),MATCH($G7,$B$2:$E$2,0))
To use the formula correctly, the absolute reference feature was used.
Conclusion
Using the VLOOKUP function with INDEX MATCH can address the limitation of returning multiple results that meet multiple criteria at the same time. In addition, aside from the PivotTable feature, INDEX MATCH is one of the most flexible formulas for rearranging data in Excel.
Finally, by using the OFFSET function to work with dynamic ranges, you can create a powerful Excel lookup formula.