Combination of INDEX and MATCH functions in Excel

INDEX and MATCH functions can be used together to create flexible and powerful formula in Excel. As said before, the INDEX function can return the value at the juncture of a given row and column. Now instead of manually setting value for the column and row, we can get help from the result of MATCH function to solve the problems with providing formula. By combining INDEX and MATCH functions in Excel, we may search for the lookup value in a column or a row and return a value in the corresponding cell. In the following we will learn more about the applications of these two functions in Excel. Consider the table below to understand easier:

If we want to find the name of the product according to its number 1398, for instance, we may use a VLOOKUP function formula like VLOOKUP(1398,A:B,2,FALSE).

But now consider the table below:

Here we do not use VLOOKUP function, but rather a combination of INDEX and MATCH functions. Because we cannot set the third parameter as -1 in VLOOKUP function. Therefore, we use INDEX MATCH function as follows:

• First we write an INDEX function and determine its first parameter as A: A, since we are going to find the name of the product which is located in Column A.
• we could give the row value to INDEX function before. The row value for the number 1398 is equal to 4. But now we want to find the row value automatically by the Excel rather than manually. So we do this using a MATCH function. Function MATCH(1398,B:B,0) will find the number 1398 in Column B and will return the corresponding row value which is equal to 4.
• Then we assemble the two functions and get this formula:

INDEX(A:A,MATCH(1398,B:B,0))

Now we can change the number 1398 with another cell or number to find the corresponding product name.

Looking for help creating advanced formulas in Excel? Please contact our Microsoft Excel technical support team.