Index Match Function in Excel

Excel Index Match Function

The INDEX Function can return the result from the row number and the MATCH function can give us the position of the lookup value in the array. This combination of INDEX MATCH Excel Function is very useful in addressing a key limitation of VLOOKUP, which is that that cannot be used to search table from left to right, however, INDEX MATCH Function can achieve this goal effortlessly. In this article, we will discuss this in detail –

What Does INDEX Function Do in Excel?

The INDEX function is used to fetch the data based on the provided row number in the array.

This is as simple as that, for now, look at the syntax of INDEX function.

Array: From which column or array we need the value??

Row Number: In the provided array from which row we need the result???

These two arguments are good enough in most of the situations. So, now we will look into the example of INDEX function.

Example of the INDEX Function in Excel

For this example consider below data.

We have data from A1 to B7 cell range. In D2 cell we have month name and for this month name, we need sales value in cell E2.

Let’s open INDEX function in cell E2.

Array is the first argument i.e. from which column we need the result i.e. we need result from “sales” column, so select from B2 to B7.

Next is ROW number i.e. in the selected range of cells from which row we need the result. In this example, we need the sales value for the month “Mar”. In the selected range “Mar” is the third row, so we need result from the third row.

Ok, that’s all. Close the bracket and hit the enter key, we will have sales value for the month of “Mar”.

Like this based on the row number provided we will get the value from the supplied array.

What Does MATCH Function Do in Excel?

The MATCH function is used to find the position of the lookup value in the supplied array. In simple terms lookup value row number or column number in the range of cells. Below is the syntax of the MATCH function.

Lookup Value: For which lookup value we are trying to find the position??

Lookup Array: In which array or range of cells we are looking for the lookup value???

Match Type: This will decide what kind of result we need. We can provide zero for an exact match.

Example of MATCH Function in Excel

For this example consider the above data only.

From the above data, we are trying to get the position of the month “Mar” in cell E5. Open MATCH function in E5 cell.

The first argument is “Lookup Value”, so here our lookup value is “Mar”, so select D5 cell.

Lookup Array is from which range of cells we are trying to look for the position of the lookup value. So select “Month” column.

The last argument is Match Type since we are looking at the exact match supply 0.

So, in the lookup array A2:A7 the position of the lookup value “Mar” is 3.

Combination of INDEX + MATCH Function in Excel

The index can return the result from the mentioned row number and Match function can give us the position of the lookup value in the array. Instead of supplying the row number to the INDEX formula we can enclose MATCH function to return the row number.

Open INDEX function in cell E2.

For the first argument Array supply B2 to B7.

For row number instead of supplying the row number as 3 open MATCH function inside the INDEX function.

Select the lookup value as D2 cell.

Select the lookup array as A2 to A7.

Enter zero as the match type.

So, based on the row number provided by the MATCH function INDEX function will return the sales value. We can change the Month name in cell D2 to see the sales value changes dynamically.

Powerful Alternative to VLOOKUP

We all have used VLOOKUP function day in day out, but one of the limitations of VLOOKUP is, it can only fetch the value from left to right not from right to left.

For an example look at the below data.

In the above data lookup value is Month and result column is sales. But in the data result column (Sales) is to the left of the lookup array table (Month), so VLOOKUP cannot help use here, but with the combination of INDEX and MATCH, we can still fetch the data from the table.

Recommended Articles

This has been a guide to Index Match Function in Excel. Here we learn how to use index & match function as a powerful alternative to VLOOKUP along with examples and downloadable excel template. You may learn more about excel from the following articles –

• Top 4 Venn Diagram Examples
• Create Box and Whisker Plot
• VBA Index Match
• How to Shade Alternate Rows in Excel?
• VLOOKUP with SUMIF
• VLOOKUP with Match Function
• VLOOKUP Tutorial 
• VLOOKUP & Pivot Table