How to Use Index Match Instead of Vlookup

Bottom Line: Learn how to use the INDEX and MATCH functions as an alternative to VLOOKUP.

Skill Level: Beginner

Watch on YouTube & Subscribe to our Channel

Download the Excel File

Here is the Excel file that I used in the video. I encourage you to follow along and practice writing the formulas.

Advantages of Using INDEX MATCH instead of VLOOKUP

It's best to first understand why we might want to learn this new formula. There are two main advantages that INDEX MATCH have over VLOOKUP.

#1 – Lookup to the Left

The first advantage of using these functions is that INDEX MATCH allows you to return a value in a column to the left. With VLOOKUP you're stuck returning a value from a column to the right.

Index Match allows you to lookup to the left

Yes, you can technically use the CHOOSE function with VLOOKUP to lookup to the left, but I wouldn't recommend it (performance test).

#2 – Separate Lookup and Return Columns

Another benefit is that you specify a single column for both the lookup and return ranges, instead of the entire table array that VLOOKUP requires.

Vlookup vs Index and Match single column

Why is this a benefit? Because VLOOKUP formulas tend to break when columns in a table array get inserted or deleted. The calculation can also slow down if there are other formulas or dependencies in the table array.

With INDEX MATCH there's less maintenance required for your formulas when changes are made to your worksheet.

Wrapping Your Head Around INDEX MATCH

An INDEX MATCH formula uses both the INDEX and MATCH functions. It can look like the following formula.

=INDEX($B$2:$B$8,MATCH(A12,$D$2:$D$8,0))

This can look complex and overwhelming when you first see it!

To understand how the formula works, we'll start from the inside and learn the MATCH function first. Then I'll explain how INDEX works. Finally, we will combine the two in one formula.

The MATCH Function is Similar to VLOOKUP

The MATCH function is really like VLOOKUP's twin sister (or brother).  Its job is to look through a range of cells and find a match. The difference is that it returns a row or column number, NOT the value of a cell.

Difference Between VLOOKUP and MATCH

The following image shows the Excel definition of the MATCH function, and then my simple definition.  This simple definition just makes it easier for me to remember the three arguments.

Match Simple Definition

The MATCH function's arguments are also similar to VLOOKUP's.  MATCH's lookup_array argument is a single row/column. Therefore, we don't need the column index number argument that VLOOKUP requires.

=MATCH(lookup_value, lookup_array, [match_type])
=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

Let's dive into an example to see how MATCH works.

An Example of the Match Function

We'll use my Starbucks menu example to learn MATCH.  In this case, we want to use the MATCH function to return the row number for “Caffe Mocha” from the list of items in column A.

MATCH Function Returns the Row or Column Number for Matching Item in List

Here are instructions on how to write the MATCH formula.

1. lookup_value – The “what” argument

Lookup_Value Argument for MATCH Function in Excel

In the first argument, we tell MATCH what we are looking for.  In this example, we are looking for “Caffe Mocha” in column A.  I have entered the text “Caffe Mocha” in cell A12 and referenced the cell in the formula.

2. lookup_array – The “where” argument.

Lookup_Array Argument for the MATCH Function in Excel

Next, we need to tell MATCH where to look for the lookup value.  I selected the range $A$2:$A$8, which contains the list of items.  MATCH will look through this column from top-to-bottom until it finds a match.

I made this an absolute reference (F4 on the keyboard) so that the range does not change if we copy the formula down. It's good to get in the habit of doing this after selecting the lookup_array range.

Note: You can also specify a row for this argument.  In that case, MATCH would look across the column from left-to-right to find a match.

3. [match_type] – The “closest/exact” match argument

Match_Type Argument for the MATCH Function in Excel

Here we specify if the function should look for an exact MATCH, or a value that is less than or greater than the lookup_value.

MATCH defaults to 1 – Less than. So we always need to specify a 0 (zero) for an exact match. This is similar to specifying FALSE or 0 (zero) for the last argument in VLOOKUP.

When your MATCH is looking up text you will generally want to look for an exact match.

If you are looking up numbers with the MATCH function then the “Less than” or “Greater than” match types can be very useful for tax and commission rate calculations.

The Result

Result of the MATCH Function in Excel

The MATCH function returns a 4. This is because it finds the lookup value in the 4th row of the lookup_array (A2:A8).

It's important to note that this is NOT the row number of the sheet. The row/column number that MATCH returns is relative to the lookup_array (range).

Now that we have a basic understanding of how MATCH works, let's see how INDEX fits in.

The Index Function

The INDEX function is like a roadmap for the spreadsheet. It returns the value of a cell in a range based on the row and/or column number you provide it.

There are three arguments to the INDEX function.

=INDEX(arrayrow_num[column_num])

The third argument [column_num] is optional, and not needed for the VLOOKUP replacement formula.

So, let’s look at the Starbucks menu again and answer the following question using the INDEX function.

“What is the price of the Caffe Mocha, size Grande?”

1. array – The “where” argument.

Array Argument for the INDEX Function in Excel

This argument tells the INDEX where to look in the spreadsheet.  I specified $C:$2:$C$8 because this range is the column of prices that I want to return a value from.

Again, it's good practice to make this range an absolute reference so you can copy the formula down.

2. row_num – The “row number” argument

Row Num Argument for the INDEX Function in Excel

Next, we specify the row number of the value we want to return within the array (range). This is the row number of the array, NOT the row number of the sheet.

For now, we can hard code this number by typing a 4 into the formula.

The Result

The result is $3.95, the value in the 4th cell of the array (range).

INDEX Formula Result in Excel

Important Note: The number formatting from the array range does not automatically get applied to the cell that contains the formula. If you see a 4 returned by INDEX, this means you need to apply a number format with decimal places to the cell(s) with the formula.

INDEX is pretty simple on its own.  Let's see how to combine it with MATCH.

Combining INDEX and MATCH

By combining the INDEX and MATCH functions, we have a comparable replacement for VLOOKUP.

To write the formula combining the two, we use the MATCH function to for the row_num argument.

In the example above I used a 4 for the row_num argument for INDEX. We can just replace that with the MATCH formula we wrote.

INDEX MATCH formula as VLOOKUP Replacement

The MATCH function returns a 4 to the row_num argument in INDEX. INDEX then returns the value of that cell, the 4th row in the array (range).

The result is $3.95, the price of the Caffe Mocha size Grande.

Result of the INDEX MATCH Formula as Replacement for VLOOKUP

Here is a simple guide to help you write the formula until you've practiced enough to memorize it.

Guide to Writing Index Match (Vlookup Replacement) Formulas

Again, you can think of MATCH as the VLOOKUP. It just returns a row number to INDEX. INDEX then returns the value of the cell in a separate column.

VLOOKUP versus INDEX MATCH

Could we have accomplished the same thing with VLOOKUP in our example? Yes. But again, the advantage of using the INDEX MATCH formulas is that it's less susceptible to breaking when the spreadsheet changes.

Inserting and Deleting Columns

If, for example, we were to add a new cup size to our coffee menu and insert a column between Tall and Grande, our Vlookup formula would return the wrong result. This happens because Grande is now the 4th column, but the index number for VLOOKUP is still 3.

VLOOKUP vs INDEX MATCH when Columns are Inserted or Deleted

You can also use the MATCH function with VLOOKUP to prevent these types of errors. However, VLOOKUP still can't perform a lookup to the left.

Look to the Left

In this case, the items could be to the right of the prices and INDEX MATCH would still work.

In fact, we don't even have to rewrite the formula if we move the columns around.

Matching Both Row and Column Numbers

I just want to quickly note that you can use two MATCH functions inside INDEX for both the row and column numbers.

Match both row and column for INDEX MATCH formula

In this example, the return range spans multiple rows and columns C4:E8.

We can use MATCH for lookups both vertically or horizontally. So, the MATCH function can be used twice inside INDEX to perform a two-way lookup. Looking up both the item name and price column.

I've added drop-downs (data validation lists) for both the Item and Size to make this an interactive price calculator. When the user selects the item and size, the INDEX MATCH MATCH formula will automatically perform the lookups and return the correct price.

The Most Common Error with INDEX MATCH Formulas

The most common error you will probably see when combining INDEX and MATCH functions is the #REF error.

This is usually caused when the return range in INDEX is a different size from the lookup range in MATCH. In the image below, you can see that the MATCH range includes row 8, while the INDEX range only goes up to row 7. When the specified criteria can't be found because of the misalignment, this will cause the formula to return an error that says #REF.

To fix the error, you can simply expand the smaller range to match the larger. In this case, we would change the INDEX range to end at cell D8 instead of D7.

INDEX MATCH will also return the #N/A error when a value is not found, just like VLOOKUP.

Using Excel Tables to Reduce Errors

The example above was easy enough to spot and fix because the data set is so small. When working with larger data sets the mismatch can occur more often because there are blank cells in the data. One workaround for that is to reference Tables instead of ranges. Here is a tutorial that explains more about Excel Tables and Structured Reference Formulas.

Conclusion

INDEX MATCH can be difficult to understand at first. I encourage you to practice with the example file and you'll have this formula committed to memory in no time.

The new XLOOKUP function is an alternative to VLOOKUP and INDEX MATCH that is easier to write. It will just be limited by availability and backward compatibility in the near future.

Please leave a comment below with questions or suggestions.

Thank you! 🙂

50 comments

Your email address will not be published. Required fields are marked *

  • Jon
    I think I’ve mentioned this before, but will say it again: It is just wrong and unfair to compare an INDEX/MATCH combo formula with a naked VLOOKUP!
    By doing so, you’re comparing apples with oranges! And raising the performance issue up front is NOT justification for continuing the unbalanced comparison, because using VLOOKUP with CHOOSE eliminates the following perceived disadvantages of VLOOKUP you’ve then raised in that unbalanced comparison: (1) the look right only (2) wide range vs single column only, and (3) risk of integrity comprise when columns are inserted within VLOOKUP’s table_array argument.
    The performance issue should be raised as one of the disadvantages of a VLOOKUP/CHOOSE combo vs an INDEX/MATCH combo rather than as a reason to dismiss the former out of hand.
    VLOOKUP/CHOOSE and INDEX/MATCH are almost identical in functionality – it’s the degraded performance of the former that elevates the benefit of using the latter, but little else.

    • Hi Col,
      I kindly disagree. With Excel, there are always many different ways to solve a problem. Part of our job as analysts is to try and find the fastest and most efficient way to produce a result. Will we always get this right the first time? Absolutely not. Part of the fun is continuing to iterate, learn, and improve.

      VLOOKUP and INDEX MATCH can both produce the same result. So, I believe it is absolutely a fair comparison. As does Microsoft.

      As I mentioned in the article, VLOOKUP and MATCH are very similar functions. They really just return a different value/property of the matching cell (item in the array).

      The new XLOOKUP function is really a combination of VLOOKUP and INDEX MATCH, giving us the best of both worlds. Microsoft points out some of the same issues I mentioned in this article in their announcement of XLOOKUP. Check out the section titled “Why release a new lookup function?”

      Here is a quote from their Facebook page on XLOOKUP compared to VLOOKUP and INDEX MATCH.

      Microsoft Excel Facebook Page XLOOKUP

      VLOOKUP CHOOSE can be used as an alternative, I just don’t recommend it because of poor performance and complexity. Most users have never seen the notation for arrays in formulas with curly brackets.

      Don’t get me wrong, I’m still a fan of VLOOKUP! I actually voted for it as my favorite in an interview Microsoft did with me on VLOOKUP versus INDEX MATCH. I’ll try to find the video. I’ll also do a follow-up post on why I like VLOOKUP. This article is highlighting scenarios where it doesn’t work, and an alternative solution with INDEX MATCH.

      I hope that helps. Thanks! 🙂

  • Hi Jon,

    Thanks for that overview. When I go to download the excel file I get taken to an error page.

    Can you fix the link and let me know when it is OK or email me the file

    Cheers

    Jeff

  • I’ve always struggled to understand Index Match. This breaks it down and it explains it step by step. Thank you!!

  • VLOOKUP can easily find the sales person, but it has no way to handle the month name automatically. The trick is to to use the MATCH function in place of a static column index.

  • Hi Jon, nicely explained.

    Using a lot of big VLOOKUPs recently, I will give this option a go.

    I would be interested in any comment you have about whether either option is better to manage size of workbook? Basically looking at options to help manage some of the fluctuations in size when retaining calculated cells.

    Cheers
    Stuart

  • Thanks vary much, Jon. The ability to look left and the ability to do a lookup on two arguments are a knock-out, as far as I can see.
    Are there any situations where Vlookup is still better? (large data sets maybe?)

  • I’m trying to employ this and didn’t have any luck. I attempted to use VLookup and it didn’t work. My question is, do the worksheets need to be in the same workbook for these to work?

    • I just tested Index and Match using totally different work book as data source and it worked. Hope you’ll get it sorted.

  • This problem is difficult to explain, but I will try. I’m creating a database to keep track of stock option spread trades that have numerous legs. Some of the trades are simple and only having two legs, with one position being long, and the other being short. But, some of the trades have 8-10 legs, and this creates a problem in calculating the value of each leg. For this example, lets open a credit spread trade and go long an option, and go short an option. This is trade number 500 and each leg is recorded on a separate row. I want to invest $1000 per leg, so we need to take the ABS difference of the two fill prices and divide it $1000. This will tell us the quantity purchased. Eventually, we’ll close the short leg, and open a new short leg. And each time, we need to calculate a quantity by performing the above calculation with the long leg. This cycle of closing the short leg and opening a new short leg can continue until the long leg is finally closed, which ends the trade. So, let’s say we have 8-legs in this trade, the first row contains the long leg, and the 7 rows below contain the short legs. Each row has an ID of 500 to identify all 8-rows as trade number 500. Each time a short leg is closed, and a new short leg is opened, we need to scan the table for ID 500, then scan those rows to find the long leg. We then need to go over 5 columns to locate the long leg fill price to use in the new quantity calculation. Hope all that makes sense! Thanks, Jeff

  • Thanks for the explanation, it worked for me. left lookup is one of the best feature of index match function.

  • Hi Jon,

    Thank you so much for your explanation, I would like to ask and follow your opinion on my issue. I am trying to build a workbook for my small business so i can track inventory. I’ve done the codes for the items (35 Items in total) and with VLOOKUP I made the received tab and sold tab. The problem now is every customer is on a separate tab which means in my sold tab I will need to read and sum the total amounts of every customer for different item…What function would be the best to sort this?
    I will really appreciate if you can help me out.

    Thanks,
    Maria

  • Having a real problem with Excel: for years, I’ve been able to create formulas using VLOOKUP and the INDEX/MATCH combo with data from 2 workbooks. Now, in Excel 2019, my formulas do not recognize other workbooks when I try to select them for the array.

    Any ideas as to why this is happening? I know that it won’t work if each workbook is in multiple instances of Excel, but both of my workbooks were opened using the same instance.

  • Great video!! I love the Match Row and Column example and the use of the drop down menu. I am wondering though…

    If I added a new size column called maxi, and input prices for the first two drinks down the menu and left the next three blank, how can I get Excel to return a “N/A” value?

    Currently, if I leave the maxi white chocolate mocha cell blank, and select it from the drop down lists, excel is returning a value of “0” in the price cell. I expect there will be some sort of true false statement, I’ve already trialled a few combinations but can’t seem to get a formula to work.

    • It will only return #N/A if it can’t find the drink a/o size, so you’d have to add something into your formula to check if the price is 0 and return #N/A if it is. Something like =IF(formula=0,”#N/A”,formula) where ‘formula’ is your existing index/match.

  • I was looking for a perfect vidoe on youtube to clear all my question on excel, I found many but none close to Excel Campus… awesome work .. keep it up ! I have a suggestion would like to see more videos on VBA and Macros.

  • Nice job on these training videos. I’m going to be focusing on pivot tables but I thought it was best to get the basics of VLookup, Index & Match. These videos were a perfect. Easy step-by-step instructions. Nice job Jon :).

  • Hello. What happens when you use these commands in the same row? For example: my column B has the stock ticker; column C tells you if it is a Buy or a Sell; and column D tells you how many stocks were bought or sold. I want to know (using index and match or the correct commands) how many stocks from column B were bought or sold; for results, I am using a table that has the tickers on a column and Buy / Sell on rows. Thanks!

  • I watched many videos on Youtube to guide me through Index/Match to no avail. Thank you for breaking it down so simply and understandably.

  • What’s the point of index match? I thought it was the formula for when you have rows with different data but the same unique identifier and wanted to match the information. From the videos I’ve seen it just looks like a formula for using ctrl + F to find a value and then read what’s in its row?

  • I have used your formula and it works great.
    I do have an issue though.
    I am making a score sheet for the Super Bowel. when I write the formula it works but if I put in a double fidget. it give me a “#N/A”
    if I insert a column and use “=RIGHT(Cell#,1)” it still does n to work. any suggestions?

  • Thanks for the post. Another big advantage is that INDEX/MATCH reference the column directly where as VLOOKUP just takes a column number for the lookup value. VLOOKUP has the issue where if you insert a new column to the lookup table then your function is now wrong, however with INDEX/MATCH the reference so your function still works.

  • Good Morning from Africa,
    am trying to use some of these functions for Payslip….
    Looking up on ID, searching whether e.g. Bonus,Overtime,Loan for this ID is relevant – (if not it should be left blank) or in the same row display the item which is relevant to the ID.
    Am just not able to combine functions for my needs.
    Maybe someone can help?
    Warm Regards

  • Wow. I have been burned more than once with VLookup. I really like the Index Match alternative. The way you presented it (simple understandable subject matter) and the terminology used to convey it and remember it, is pretty good.
    My only ‘druther’ would have been to include one more extension to the formula by adding an error message to the user when N/A is the result. Like, ‘when NA happens, show ‘name/item not found” or something like that.
    THANK YOU VERY MUCH. This WILL get used in my new budget worksheet.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools