**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:

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.

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))`

3) Upon pressing `CTRL-ENTER`

, the `INDEX`

and `MATCH`

Formula returns the value of `Bradley Messer`

in cell `J3`

as shown below.

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.

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))`

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.

**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))`

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.

**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))`

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.

**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