For those of you who are well versed in Excel, you are probably very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in another cell based on matching text in the same row.
If you are not familiar with VLOOKUP feature, you can check my previous post at how to use VLOOKUP in excel†
as powerful as it is, VLOOKUP has a limitation on how the matching reference table must be structured for the formula to work.
This article will show you the limitation where: VLOOKUP cannot be used and introduces another function in Excel called INDEX MATCH that may solve the problem.
INDEX MATCH Excel Example
Using the following example Excel spreadsheet, we have a list with the name of the car owner and the name of the car. In this example we are trying to get the Car ID based on the car model listed under multiple owners, as shown below:
On a separate sheet called Car brandwe have a simple car database with the ID card† car model and Colour†
With this table arrangement, the VLOOKUP function can only work if the data we want to retrieve is in the column to the right of what we are trying to match (car model field).
In other words, with this table structure, because we’re trying to match it based on the car modelthe only information we can get is: Colour (Not ID card as the ID card column is to the left of the car model column.)
This is because VLOOKUP requires the lookup value to be in the first column and the lookup columns to the right. In our example, none of these conditions are met.
The good news is, INDEX MATCH will be able to help us with that. In practice, this is basically combining two Excel functions that can work separately: TABLE OF CONTENTS function and CONTEST function.
However, for the purpose of this article, we will only talk about the combination of the two for the purpose of the function of VLOOKUP†
The formula can seem a bit long and intimidating at first. However, once you’ve used it a few times, you’ll memorize its syntax.
Here’s the full formula in our example:
Here’s the breakdown for each section
=INDEX( – The † indicates the beginning of the formula in the cell and TABLE OF CONTENTS is the first part of the Excel function we use.
Car Type!$A$2:$A$5 – the columns on sheet Car brand where the data we want to retrieve is located. In this example, the ID card of each car model.
CONTEST( – The second part of the Excel function we use.
B4 – The cell that contains search text that we are using (car model††
Car Type!$B$2:$B$5 – The columns on sheet Car brand with the data we will use to match the search text.
0)) – To specify that the search text must exactly match the text in the corresponding column (e.g. Car Type!$B$2:$B$5† If the exact match is not found, the formula returns #AFTER†
Remark: remember the double closing bracket at the end of this function † and the commas between the arguments.
Personally, I’ve moved away from VLOOKUP and now use INDEX-MATCH because it can do more than VLOOKUP.
The INDEX MATCH features also have other advantages compared to: VLOOKUP†
- Faster calculations
When working with large datasets where the calculation itself can take a long time due to the many VLOOKUP functions, you will notice that once you replace all of these formulas with INDEX-MATCH, the overall calculation is calculated faster.
- No need to count relative columns
If our reference table contains the key text we want to find in the column C and the data we need is in the column AQwe need to know/count how many columns are between column C and column AQ when using VLOOKUP.
The INDEX-MATCH functions allow us to directly select the index column (ie column AQ) where we need to get the data and select the column to match (ie column C).
- It looks more complicated
VLOOKUP is very common these days, but not many people know how to use the INDEX-MATCH functions together.
The longer string in the INDEX-MATCH function makes you look like an expert at handling complex and advanced Excel functions. Enjoying!