Index Match Function in Excel (Use this Powerful Alternative to VLOOKUP)

What Does INDEX Function Do in Excel?

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 the INDEX MATCH Excel Function is very useful in addressing a key limitation of VLOOKUP, which is that that cannot be used to search the table from left to right; however, INDEX MATCH Function can achieve this goal effortlessly. In this article, we will discuss this in detail –

You can download this Index Match Function Excel Template here – Index Match Function Excel Template

This is as simple as that, for now, look at the syntax of the 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 situations. So, now we will look into the example of the INDEX function.

Example

For this example, consider the below data.

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

Let’s open the INDEX function in cell E2.

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

Next is the 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 results 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 the 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 the “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 the 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 the 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 the MATCH function inside the INDEX function.

Select the lookup value as a 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 the 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 example, look at the below data.

The above data lookup value is Month, and the 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.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Index Match Function in Excel