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

**Skill Level: **Beginner

## 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.

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.

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.

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.

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.

Here are instructions on how to write the MATCH formula.

**1. lookup_value** – The “**what” **argument

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.

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

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

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(array,row_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.

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

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

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

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.

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

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.

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.

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! 🙂

Please send a new link to dơnload Excel File.

Thanks.

Hi Hai,

I’m sorry about that. I believe the download is working now. Let us know if you experience any issues. Thanks!

link to workbook not working

never mind working now

Thanks for letting us know Jim. And I apologize for the inconvenience.

Link to the Excel file is broken

Hi Don,

I’m sorry about that. It should be working now.

Hi John,

the file cannot be downloaded.

Hi Guilian,

I’m sorry about that. It should be working now.

Excellent explanation. Keep it up.!!!!

Thank you, Mohammed! I appreciate your support. 🙂

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.

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

Hi Jeff,

I’m sorry about that. The download should be working now. Let us know if you still have issues.

Thank you for the nice feedback! 🙂

Again well presented, thank you for this useful tool.

Thank you, Mark! 🙂

Hi Jon,

Thank you so much for the training!

Sam.

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

Vlookup creates bigger file size. Also, using tables and named ranges ensures that you do not have to update the formula when adding new columns or rows of data.

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

Can “” be used some how with index match

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.

Thanks, Great Job.

[…] The INDEX MATCH functions look up and return the value of a cell in a selected array, using 1 or 2 reference values. INDEX MATCH is like VLOOKUP, but INDEX MATCH is more robust and flexible. […]

Thanks for the very helpful instruction, Jon. Cheers.

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 :).

THANK YOU! I finally understand this from your explaining through comparison to vlookup.

Nice

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!

Really great article explaining INDEX + MATCH. Thanks for writing this.

I am happy, Nice presentation

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?

Thanks for your Exel Documents.

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.

It’s a great and simple lesson! Bravo!!

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.

Great explanations and very helpful. I really appreciate.