This lesson shows you how to write formulas using INDEX and MATCH to let you perform lookups that VLOOKUP can't, and which run much faster on large spreadsheets than VLOOKUP. This lesson explains how INDEX and MATCH work on their own, and then shows you how to write an INDEX MATCH formula that can look left as well as right, and performs much faster than VLOOKUP on large tables.

INDEX MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right (which is all VLOOKUP can do).

Note - Microsoft have announced a new function, XLOOKUP, which does the same job as the INDEX/MATCH workaround described in this lesson. It's in beta right now, and is only available on Office365. You can read more about how to use XLOOKUP here.

What you'll learn in this lesson

In this tutorial, you'll learn:

• How to use the MATCH function
• How to use the INDEX function
• Scenarios where VLOOKUP doesn't work or isn't the best solution.
• How to combine the INDEX and MATCH functions in your formulas to perform lookups that VLOOKUP can't.

Why INDEX MATCH is so much better than VLOOKUP?

For many users, VLOOKUP just does what they need. But there are three scenarios where VLOOKUP falls short. That's where INDEX MATCH comes in handy:

• VLOOKUP can only look from left to right.
  • You look a value in one column in a table, and then return a value from a column to the right.
  • But what happens if you want to look from right to left? VLOOKUP simply can't do that. INDEX/MATCH can.
• VLOOKUP is prone to error, especially in big spreadsheets
  • With VLOOKUP, you specify a number to represent which column you want to return the value from. So you have count the columns to figure out which one you want. Hopefully you'll notice if you get it wrong but what if you don't? Finding and debugging this error can be a nightmare.
  • With INDEX MATCH, you select the specific column of data from which you want to return the value. That's much more reliable and easier to debug when things go wrong.
• VLOOKUP slows down big spreadsheets.
  • When the value you are looking up is in one column, and the value you want to return is in the 30th column in the table, VLOOKUP requires you to select all 30 columns. This results in a lot of extra computations and can bring large spreadsheets to their knees.
  • With INDEX/MATCH, you select the column containing the lookup value and the column containing the return value, and nothing else. That makes INDEX/MATCH much more efficient.

Recap of what the INDEX function does.

The INDEX function has a very simple job - look in a column of data and return a value from the row that you specify.

Here's an example of the INDEX function in action:

=INDEX(A2:A8,3)

In this example, we tell Excel to return the 3rd value in the range A2:A20. Here's what that looks like:

Note that I've changed the formula from above to use the value in B11 as the row to return, rather than typing 3 directly into the INDEX function.

You can read more about the INDEX function in these lessons: How to use the INDEX function to find data in an Excel table and Use INDEX to lookup multiple values in a list. Both links open in a new tab.

Recap of what the MATCH function does

The MATCH function also has a simple job - look in a column of data and return the row number of a value that you specify. It's a bit more complicated than the INDEX function, but we'll use it in a simple way in this lesson. You can find out more about the MATCH function in this lesson: Use the MATCH function in Excel to find the position of a value in a list (opens in a new tab).

Here's an example of the MATCH function in action. Let's assume we have a column of names, and we want to know which row Birgit is found in. We'll assume that each name appears only once in the list - this will make things simple for us.

=MATCH("Birgit",B2:B8,0)

Here's a worked example to illustrate the use of the MATCH function:

As you'll see, I've adjusted the example to use the value in B11 as the value to look up, rather than typing "Birgit" directly into the formula.

Note that the third parameter in the formula is the match_type. By entering zero as the last parameter in the formula, we tell Excel we are looking for an exact match. The VLOOKUP function has a similar parameter, but the MATCH function uses this parameter differently. I won't go into the differences here, but if you're planning to use the MATCH function a lot, you should read our lesson on how to Use the MATCH function in Excel to find the position of a value in a list which covers the match_type parameter in detail.

Putting INDEX and MATCH together

Now that we understand what the INDEX and MATCH functions do on their own, let's put them together to create an INDEX MATCH formula that can replace VLOOKUP - and do much more..

Example 1 - Use INDEX MATCH to replace VLOOKUP

Let's start with a simple example, and use INDEX/MATCH to do something that VLOOKUP can do - find the name of the person from France.

First, here's the VLOOKUP function you would use to do this:

=VLOOKUP("France",A2:B8,2,0)

This formula says "Look for France in column A and if you find it, return the value from the second column in the same row. If you don't find France, return an error"

Here's how we can do this with INDEX/MATCH:

=INDEX(B2:B8,MATCH("France",A2:A8,0))

This formula says "Find the row that contains France in column A, and then get the value in that row in column B. If you don't find France, then return an error".

Here's our example with this formula combining INDEX and MATCH:

Once again, note that I'm using B11 in my formula rather than typing "France" directly into the formula.

Example 2 - Using INDEX MATCH to look from right to left.

Obviously, when compared to the VLOOKUP version of the formula in the example above, using INDEX MATCH results in a longer formula. For that reason, you may prefer to keep using VLOOKUP rather than INDEX/MATCH for simple examples.

However, what if we want to do a lookup from right to left, instead of left to right? For example, what if we know the person's name, but want to find out what country they are from?

Let's now look at how we can use INDEX MATCH to do a lookup from right to left, and find out the country a person is from.

Here's an updated version of the previous example:

Unsurprisingly, the formula is almost unchanged. We're still finding an item in a list (Melodie) and then retrieving the value in the same row from another column (France).

Why INDEX MATCH is faster than VLOOKUP

At the start, I stated that INDEX MATCH is faster than VLOOKUP.

The reason this is so isn't immediately obvious with the simple examples provided here. However, consider this scenario:

• Our INDEX MATCH formula in Example 1 looks like this:
  • =INDEX(B2:B8,MATCH("France",A2:A8,0))
• As you can see, this formula specifies the lookup column (A2:A8) and the value column (B2:B8), whereas the VLOOKUP function doesn't explicitly specify the value column - it simply gives a number that defines where the value column is relative to the lookup column:
  • =VLOOKUP("France",A2:B8,2,0)
• But what if our value column were in row AX (column 50) ? Here is how our two formulas would look:
  • =VLOOKUP("France",A2:B2,50)
  • =INDEX(AX2:AX8,MATCH("France",A2:A8,0))
• Our VLOOKUP formula is going to have to count across 50 columns to find the lookup value - this is going to take time, particularly if you have a spreadsheet with hundreds or thousands of rows, each containing this VLOOKUP formula. If you've worked with a spreadsheet like that you'll be aware that it can take several seconds to recalculate the spreadsheet.
• By comparison, our INDEX function already knows where it needs to look, so it is able to complete its task a lot faster than VLOOKUP. This is good for your productivity since you don't have to wait for the recalculation each time you change a value in the spreadsheet.
• Add to that the fact that our INDEX function is much easier to understand by looking at it - and troubleshoot - than the VLOOKUP formula, and you'll see that INDEX MATCH is also less prone to errors.

Summary - using INDEX MATCH as an alternative to VLOOKUP

In this lesson, we've learnt why VLOOKUP isn't always the best choice when trying to find values in a table based on a lookup value.

• We've looked at an example where INDEX MATCH can do something VLOOKUP can't do (look to the left in a table as well as the right).
• We also talked about how much more efficient and less error-prone the INDEX MATCH combination can be compared to VLOOKUP.

Hopefully this lesson has helped you understand how to combine the INDEX MATCH functions. If you have any feedback on this lesson, or specific questions in relation to the use of INDEX MATCH, feel free ask them in the comments below.