Index Match Function Excel [Tutorial Video] (16:32)

The Index Match function combination in Excel is the best way to retrieve data from ranges of cells in Excel.

The Index Match combo gets around the limitations of functions like VLOOKUP and HLOOKUP, and it’s faster and far more flexible than either of these.

It’s arguably even better than the newer XLOOKUP function because you can write a single INDEX function to search an entire range rather than having to write “double” XLOOKUP functions, with one inside the other.

You use the Index Match Function Excel combination to retrieve specific items in data analyses and to set up scenarios and lists of comparable public companies and precedent transactions in financial models.

Let’s start by looking at a few simple examples of how it works:

Index Match Function Excel: Starting with INDEX

Let’s say that we have a list of sales representatives (sales reps) for a company in a spreadsheet called “Sales_Reps”.

We could use the INDEX function to retrieve information from a specific row and column number, using this syntax:

=INDEX(Array, Row Number, Column Number)

Since the 3rd row is for “Cletus Richie” and the 7th column is for “Hire Date” this function retrieves Cletus Richie’s Hire Date:

Since we have to specify the exact row number and column number in this range, though, this function is worse than VLOOKUP and HLOOKUP – at least those functions find the item in a range of data!

If the range of cells you’re indexing has only 1 column, the “Column Number” part of the INDEX function is optional:

But if the range has more than 1 column, leaving out the Column Number will generate a #REF! error.

By itself, INDEX is not very useful because of the need to specify the exact row and column numbers.

Combining INDEX with MATCH – usually two MATCH functions – though, makes it an incredibly useful function combination in Excel.

The MATCH Function in Excel

The MATCH function finds the row or column number of an item in a range of cells, and then it passes those row and column numbers into INDEX.

Here’s an example of how MATCH works by itself, using the following syntax:

=MATCH(Lookup Value, Lookup Array, Match Type)

You normally set “Match Type” to 0 for an Exact Match:

This MATCH function returns 4 since “State” is in the 4th position of this row at the top.

This 4 is the relative position of “State” in the row – not its absolute position, which is column #5 (column E)!

We can also use MATCH to find items in a column:

This function returns 10 since the Sales Rep with a base salary of $89,00 is in the 10th row of this specific column.

Index Match Function Excel: Combining INDEX and MATCH

When you combine INDEX and MATCH, the size of the range in the INDEX function must match the sizes of the ranges in the MATCH functions.

Here’s an example where we search for the name of Sales Rep #6:

This produces “Sylvia Marin” because that is the name of the Sales Rep with ID 6. The function here is:

=INDEX(B2:K11,MATCH(6,G2:G11,0),MATCH(“Name”,B2:K2,0))

Here is what happens internally:

First, Excel looks in the G2:G11 column for the number 6. It needs an exact match because of the 0 in this function: MATCH(6,G2:G11,0).

It finds this number 6 in row 7 of this range.

Then, Excel looks in row B2:K2 for “Name” and it once again needs an exact match because of the 0 in this function: MATCH(“Name”,B2:K2,0).

It finds “Name” in column 1 of this range.

Then, Excel replaces these MATCH functions with the numbers 7 and 1 instead:

And this produces the “answer” of Sylvia Marin:

Unlike VLOOKUP and HLOOKUP, this Index Match Function Excel combination can search in any row or column in the range of cells – not just the leftmost column or topmost row.

It also works even if the range of cells changes (i.e., rows or columns are added or deleted) because it always searches for specific items in the reference row or column:

The only disadvantage is that the INDEX/MATCH/MATCH combination takes a bit more time to set up than VLOOKUP or HLOOKUP, but it’s so much more flexible that the additional time requirement doesn’t even matter.

Also, you must be very careful with the ranges used in the INDEX function and the two inner MATCH functions, or this combination will not work properly:

Rewriting a VLOOKUP Function with the INDEX and MATCH Functions

We can now use INDEX and MATCH to rewrite previous VLOOKUP and HLOOKUP functions and make them more flexible.

For example, we previously used VLOOKUP to calculate the Commissions owed to each sales rep for each sale in a table of customer orders.

But we can now rewrite it using the INDEX/MATCH/MATCH combination:

What’s the advantage?

Now, if we ever change the position of the “Commission Rate” column in the Sales_Reps spreadsheet, or we insert or delete columns, this function will still work:

This may seem like a minor point, but spreadsheets change all the time as you go through different versions and processes.

The other big advantage is that with VLOOKUP, we must start in column G of the Sales_Reps spreadsheet since that’s the one that contains the Sales Rep IDs.

But with INDEX/MATCH, there are no such requirements, so we can INDEX the entire range and then decide which row and column to search later on.

This point is a bigger advantage because there are many, many cases in which you need to find an item and then move to the left, or find an item and move up – or move in some other directions.

With VLOOKUP and HLOOKUP, you can’t do that at all – but it’s easy with INDEX and MATCH.

Other Uses of INDEX and MATCH in Excel

Outside of data analysis, we also use the INDEX/MATCH combination all the time in financial models. Here are a few examples:

First, we often use this function combo to set up scenarios in models and select the appropriate number based on the selected scenario:

Notice here that the range used in the MATCH function does not have to be “inside” the indexed area – it just needs to be the same height as the indexed area, i.e., it must have the same number of rows.

This is what the concept of “alignment” means – you don’t necessarily need an exact match, but the number of rows and number of columns in the inner MATCH functions must match the number of rows and columns in the indexed range.

We also use INDEX and MATCH for retrieving data from “calculations” sheets, such as those for comparable company analysis (CCA):

In this case, we typically index a huge range that has data and calculations for each company in this set, such as the area shown below:

Once again, the key advantage is flexibility.

By setting it up this way, if something ever changes, the same INDEX and MATCH functions will work.

If we used HLOOKUP or VLOOKUP instead, we would have to change the row and column numbers, the starting and ending points of the range, and so on.

Finally, the INDEX/MATCH combo is great because they are both non-volatile functions, meaning that they are not recalculated when something in the spreadsheet changes… unless that change directly affects those functions.

By contrast, Excel will do a forced recalculate of HLOOKUP and VLOOKUP whenever something changes – even if it’s small and unrelated to them.

The Bottom Line: The Index Match Function Excel combination is one of the most powerful tools in your spreadsheet toolbox.

Use it often and use it well, and you’ll save hours and hours in spreadsheets.