Excel: How to Use INDEX and MATCH in One Formula

Excel: How to Use INDEX and MATCH in One Formula

 

Level: Advanced

Download the Excel Files Here to Follow Along

 

Many of you probably already use the VLOOKUP Function in Microsoft Excel in order to lookup data. We have covered both the standard VLOOKUP Function and the more advanced double VLOOKUP Formula in previous tutorials – for those of you that need a quick refresher, you can go over those tutorials.

We are now going to look at the advanced lookup functionality, provided by using the INDEX and MATCH Functions in one formula. This INDEX and MATCH combination can be used to lookup data in columns and rows, it can also lookup data to the left and the right, and is thus much more versatile than the VLOOKUP Function.

So, let’s see how to use the INDEX and MATCH Functions together (within one formula) in the following examples.

INDEX and MATCH Formula

A hypothetical baseball scout visited a few different states, created a list of players, and recorded his evaluation of their skill level in an Excel workbook. He recorded their Player ID (the unique identifier that identifies each player), their names, the state they are from, their ages and their overall rating.

The baseball scout calculated the overall rating by looking at a combination of factors namely the batting average, hitting power and speed of the player at hand and based on the above factors, gave the player a rating of good, fair or poor. This is the same example we used in our basic VLOOKUP tutorial and we are now going to see how to use the INDEX and MATCH formula to obtain the same result needed.

The source data is shown below:

 

excel index and match

 

1) We’d like to enter/specify the Player ID in cell I3 and then have the INDEX and MATCH Formula return the name of the player in cell J3. So, we enter the Player ID in cell I3 as shown below.

 

excel index formula

 

2) We would like to retrieve the matching name of the player in cell J3, based on the Player ID given in cell I3, so we enter the following formula in cell J3:

=INDEX(B4:B19,MATCH(I3,A4:A19,0))

 

Microsoft Excel index and match formula

 

3) Upon pressing CTRL-ENTER, the INDEX and MATCH Formula returns the value of Bradley Messer in cell J3 as shown below.

 

excel index match

 

So let’s review this process in a little bit more detail:

  • Our array or column for the INDEX Function that we want to get the ultimate data from is column B (B4:B19), which contains our Player Names.
  • The MATCH Function which is nested, within the main INDEX Function takes the lookup value in cell I3, and finds its relative position within the Player ID column i.e column A (A4:A19).
  • This position is then used by the INDEX Function to find the matching Player Name in column B, based on the position delivered by the MATCH Function.

 

excel index and match formula

 

4) Let’s practice a little bit more, just to make sure we’ve got the hang of the INDEX and MATCH Formula. Now we’d like to enter the Player ID in cell I7 and this time have the corresponding rating shown in cell J7. So, we enter the Player ID in cell I7 as shown below.

 

 

5) In this case, we’d like to retrieve the rating of the player in cell J7, based on the Player ID given in cell I7, so we enter the following formula in cell J7:

=INDEX(E4:E19,MATCH(I7,A4:A19,0))

 

excel index match formula

 

6) Upon pressing CTRL-ENTER, the INDEX and MATCH Formula returns the value of Poor in cell J7 as shown below.

 

 

There you have it.

 

test your understanding

 

What does the nested MATCH function in the INDEX and MATCH formula deliver, i.e what does it return?

 

Finding the Associated Maximum and Minimum Value Using the INDEX and MATCH Formula

 

Finding the Associated Maximum Value

A hypothetical social media manager working for a popular recipes blog, has posted some articles on the blog and recorded the view count for each article in a Microsoft Excel workbook. The social media manager wants to determine the name of the article with the highest views in one cell automatically, using a formula and the name of the article with the lowest views in another cell, automatically using a formula.

So, he decides that he is going to use the INDEX and MATCH Formula in combination with the MAX Function, in order to determine the name of the article with the most views. In another cell, he will then use the INDEX and MATCH Formula in combination with the MIN Function in order to determine the name of the article with the least views.

The source data is shown below:

 

 

1) We’d like to use the INDEX and MATCH Formula in combination with the MAX Function in order to get the name of the article with the highest views in cell E3. So, we can see that this is based on view count and then retrieving the actual name of the article that corresponds to the highest view count.

2) In order to retrieve the name of the article with the highest views, in cell E3, we enter the following formula:

=INDEX(A8:A14,MATCH(MAX(B8:B14),B8:B14,0))

 

index match excel

 

3) Upon pressing CTRL-ENTER, the INDEX and MATCH formula, in combination with the MAX Function returns the value Baked Macaroni and Cheese Recipe in cell E3, as shown below.

 

 

So let’s review this process in a little bit more detail:

  • Our array or column which our INDEX Function needs to get the ultimate data, is from column A (A8:A14) which contains the names of our actual articles.
  • The MATCH Function which is nested within the main INDEX Function, this time is not given an actual value, hard-coded or as a cell reference from us as a lookup_value, instead the MAX Function is looking at the view count and then using the maximum view count as the lookup_value.
  • The MATCH Function then delivers the position of this maximum view count to the formula.
  • This position is then used by the INDEX Function to obtain the name of the article in column A, based on the position delivered by the MATCH Function.

 

test your understanding

 

Adapt the above given formula to find the name of the article with the second highest views (hint: use the LARGE Function within the INDEX and MATCH Formula).

 

Finding the Associated Minimum Value

Using the same social media example given above, we now want to find the name of the article with the lowest view count.

1) We’d like to use the INDEX and MATCH Formula in combination with the MIN Function in order to get the name of the article with the lowest views in cell E5. So we can see that this is based on view count and then retrieving the actual name of the article that corresponds to the lowest view count.

2) In order to retrieve the name of the article with the lowest views, in cell E5, we enter the following formula:

=INDEX(A8:A14,MATCH(MIN(B8:B14),B8:B14,0))

 

min index match excel

 

3) Upon pressing CTRL-ENTER, the INDEX and MATCH formula in combination with the MIN Function returns the value Cheese and Stuffed Pepper Casserole Recipe in cell E5, as shown below.

 

index match min excel

 

test your understanding

 

Adapt the above given formula to find the name of the article with the second lowest views (hint: use the SMALL Function within the INDEX and MATCH Formula)

 

Conclusion

While having to learn two functions, namely both the INDEX and MATCH Function in order to use the combination formula is admittedly more complicated than just learning one VLOOKUP Function – there are times that you will encounter lookup situations that the VLOOKUP cannot handle. The INDEX and MATCH Formula has additional advantages in comparison to the VLOOKUP Function, you can look through both rows and columns and further extend the functionality easily, by using other functions within the INDEX and MATCH Formula. It is also faster than VLOOKUP in spreadsheets with large data sets with many rows.

Learn more about the INDEX and MATCH Formula and other useful syntax:

Review the syntax of the MAX Function

Review the syntax of the MIN Function

Review the syntax of the LARGE Function

Review the syntax of the SMALL Function

Review the syntax of the INDEX Function

Review the syntax of the MATCH Function

How to use INDEX and MATCH