For those of you who are good with Excel, you are probably very familiar with VLOOKUP. The VLOOKUP function is used to find a value in another cell based on some matching text in the same row.
If you are not familiar with VLOOKUP yet, you can check out my previous post on how to use VLOOKUP in Excel
As powerful as it is, the VLOOKUP function has a limitation on how the associated lookup table must be structured for the formula to work.
This article will show you the limitation of using VLOOKUP and introduce another function in Excel called INDEX-SEARCH that can solve this problem.
Example of index matching in Excel
Using the following example Excel spreadsheet, we have a list of car and vehicle owner names. In this example, we will try to get the vehicle ID based on the vehicle model specified for multiple owners as shown below:
– /
On a separate sheet called CarType, we have a simple car database with ID, car model and color.
With such a table setup, the VLOOKUP function can only work if the data we want to get is located in the column to the right of what we are trying to match (the “Car model” field).
In other words, with this table structure, since we are trying to map it based on the car model, the only information we can get is the color (not the ID, since the ID column is to the left of the car model column).
This is because when using VLOOKUP, the search value must appear in the first column and the search columns must appear to the right. In our example, none of these conditions are met.
The good news is that INDEX-MATCH can help us with this. In practice, this is actually a combination of two Excel functions that can work separately: the INDEX function and the SEARCH function.
However, in this article, we will only talk about the combination of these two elements in order to reproduce the VLOOKUP function.
The formula may seem a little long and daunting at first. However, after using it several times, you will learn the syntax by heart.
This is the complete formula in our example:
= INDEX (CarType! $ A $ 2: $ A $ 5, MATCH (B4, CarType! $ B $ 2: $ B $ 5.0))
Here is a breakdown for each section
= INDEX (- “=” stands for the beginning of a formula in a cell, and INDEX is the first part of the Excel function we use.
CarType! $ A $ 2: $ A $ 5 – the columns in the CarType sheet that contain the data we would like to receive. In this example, the identifier for each car model.
SEARCH (is the second part of the Excel function we are using.
B4 – The cell containing the search text we are using (Car Model).
CarType! $ B $ 2: $ B $ 5 – the columns in the CarType sheet with the data that we will use to match with the search text.
0)) – to indicate that the search text must exactly match the text in the corresponding column (for example, CarType! $ B $ 2: $ B $ 5). If no exact match is found, the formula returns # N / D.
Note: Remember the double closing parenthesis 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 can do more than VLOOKUP.
The INDEX MATCH functions also have other advantages over VLOOKUP:
- Faster calculations.
When we work with large datasets, where the calculation itself can take a long time due to the large number of VLOOKUP functions, you will find that after replacing all of these formulas with INDEX-MATCH, the overall calculation will be faster.
- Don’t count relative columns
If our lookup table has a key text that we want to look for in column C and the data we need to get is in column AQ, we will need to know / count how many columns are between column C and column AQ when using VLOOKUP.
With INDEX-SEARCH functions, we can directly select the index column (for example, column AQ) in which we need to get data, and select the column to match (for example, column C).
- It looks more complicated
Currently, VLOOKUP is quite common, but not many people know about the joint use of the INDEX-SEARCH functions.
The longer string in the INDEX-SEARCH function helps you look like an expert with the advanced and complex Excel functions. Enjoy!
–