When to Use Index-Match Instead of VLOOKUP in Excel

We’ll show you with an example

For those of you who are well-versed in Excel, you are most likely very familiar with theVLOOKUPfunction. TheVLOOKUPfunction is used to find a value in a different cell based on some matching text within the same row.

If you are still new toVLOOKUPfunction, you can check out my previous post onhow to use VLOOKUP in Excel.

As powerful as it is,VLOOKUPhas a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation whereVLOOKUPcannot be used and introduce another function in Excel calledINDEX-MATCHthat can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab theCar IDbased on theCar Modellisted under multiple owners as shown below:

On a separate sheet calledCarType, we have a simple car database with theID,Car ModelandColor.

With this table setup, theVLOOKUPfunction can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Modelfield).

In other words, with this table structure, since we are trying to match it based on theCar Model, the only information that we can get isColor(NotIDas theIDcolumn is located to the left of theCar Modelcolumn.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is,INDEX-MATCHwill be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually:INDEXfunction andMATCHfunction.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function ofVLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

Here is the breakdown for each section

=INDEX(– The“=”indicates the beginning of formula in the cell andINDEXis the first part of the Excel function that we are using.

CarType!$A$2:$A$5– the columns on sheetCarTypewhere the data we would like to retrieve is contained. In this example, theIDof eachCar Model.

MATCH(– The second part of the Excel function that we are using.

B4– The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5– The columns on sheetCarTypewith the data which we will use to match against the search text.

0))– To indicate that the search text has to exactly match with the text in the matching column (i.e.CarType!$B$2:$B$5). If the exact match is not found, the formula returns#N/A.

Note: remember the double closing bracket at the end of this function“))”and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

TheINDEX-MATCHfunctions also have other benefits as compared toVLOOKUP:

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

If our reference table is having the key text that we want to search in columnCand the data that we need to get is in columnAQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications.Read Aseem’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

When to Use Index-Match Instead of VLOOKUP in Excel#

INDEX MATCH Excel Example#

When to Use Index-Match Instead of VLOOKUP in Excel

INDEX MATCH Excel Example