When it comes to the Excel world, it’s divided into two factions – the VLOOKUP brigade and the INDEX/MATCH regiment.
If you’re wondering which one scores higher in the VLOOKUP vs INDEX/MATCH battle, this article will try and answer it.
During one of my Excel Training sessions, I was surprised to find out that most of the people were aware and, in some cases, proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combo.
And when I mentioned it, they had a lot of queries:
- Is it a lookup and reference formula? – Yes! Maybe the best of the lot
- Is it a new Excel 2016 function? – Hell NO!
- Can it reference data between different worksheets or workbooks? – Yes
- Is it better than VLOOKUP? – It depends
I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH.
In this tutorial, I will try and compare these two formulas as objectively as I can.
This Tutorial Covers:
I don’t have favorites, to begin with, but I prefer using the INDEX/MATCH combo more than VLOOKUP. The choice is driven by a lot of factors including what the data looks like and how it would be used. I will cover all these in this article.
VLOOKUP Vs INDEX MATCH – An Objective Comparison
So let’s get started and put an end to this age-long debate of VLOOKUP vs INDEX/MATCH and which one is better.
And to do this, I will compare these two functions on some parameters (some are quantifiable and some are not).
The popularity of the function
VLOOKUP takes this hands down.
For some people, if you know how to use VLOOKUP, you know how to use Excel. And given how much can be done with this single function, I don’t blame them.
For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users.
Although this is not a popularity contest, it plays a huge role in why VLOOKUP gets used so much.
Imagine you’re working with a client/manager who is proficient in using VLOOKUP but doesn’t know how to use INDEX/MATCH. It makes more sense to use a function which both you know, instead of trying to teach them about INDEX/MATCH.
Ease of USE
The reason VLOOKUP is so popular is that it’s easy to use (at least when compared with INDEX/MATCH).
When I take Excel training, I would always start by first covering VLOOKUP first. A lot of people find VLOOKUP hard, so I can’t even imagine trying to teach them INDEX/MATCH (unless they already know how to use VLOOKUP proficiently).
And in most cases, VLOOKUP is good enough. It can do most of the things people need when working with data. Surely you can do the same thing with INDEX/MATCH too, but why take the hard road when it’s not even needed.
VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH.
INDEX & MATCH are two separate functions that take three arguments each and should be combined to do a lookup (getting complicated already??).
While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP.
The flexibility of the Function
VLOOKUP is a great function but has a few limitations:
- It can not lookup and return a value which is to the left of the lookup value.
- It works only with data which is arranged vertically.
- VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place.
Before you start fuming with rage and leave a comment about how VLOOKUP can do all these things, hear me out. VLOOKUP, as a stand-alone function, is not meant to do this. Of course, you can combine it with other formulas and get around all these things, but then it loses the tag of being easy to use.
If someone can use a combination to formulas to make VLOOKUP look to the left or make columns dynamic, I am sure that person is better off using INDEX/MATCH, which is made to handle these things with ease.
So yes, VLOOKUP can get around these limitations, but that’s not worth it. With more coding and robotics, I am sure you can also make VLOOKUP fetch your favorite coffee, but remember, it’s not made for this.
INDEX-MATCH combo, on the other hand, is made to handle all these issues. It can
- Lookup and return a value which is to the left of the lookup value,
- Handle both horizontally and/or vertically structured data.
- Handle row/columns numbers in case you insert or delete some from the dataset
Speed of the formula
The difference is hardly noticeable when you have small data sets.
But if you have thousands of rows and many columns, this can be a deciding factor.
Without reinventing the wheel, here is a post by Charley Kyd, where he clearly mentions:
At its worst, the INDEX-MATCH method is about as fast as VLOOKUP; at its best, it’s much faster.
While I have considered this as one of the factors, I believe it’s less important than others. Over the years, Microsoft has been hard at work trying to improve the speed of these functions, and they have made considerable improvements since I wrote this article first.
Here is an update where they mention how they are making formula such as VLOOKUP, HLOOKUP, and MATCH faster.
Also, it’s a very small percentage of the number of people who can actually benefit from the speed improvement that comes with using INDEX/MATCH over VLOOKUP.
Overall Verdict (VLOOKUP Vs INDEX/MATCH Combo)
Although I am a huge fan of INDEX/MATCH, in all fairness I must admit, VLOOKUP is still the King.
This doesn’t mean that you should not learn about INDEX/MATCH, but if you’re new to Excel and lookup formulas, start with VLOOKUP. Use it and master it first, and then move to INDEX/MATCH
INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula.
That being said, VLOOKUP is not going anywhere and is likely to remain as one of the most popular functions in Excel for ages to come.
Well, the debate is not exactly over (see the comments section). Fuel the fire – leave your 2 cents in the comments section.
The Difference Between VLOOKUP and INDEX/MATCH
Before getting to the comparison, I think it’s important to know the basic difference (and more importantly the similarities) between these two functions.
Both VLOOKUP and INDEX/MATCH are formulas you can use to look up a value in a dataset and fetch the corresponding value (just like you scan a menu and look for the price of the dish you want to order).
In most cases, you can use these interchangeably.
The main differences in these functions become more important when you need a little more than a simple lookup and fetch operation.
Here are some basic differences in these two formulas (more covered in detailed later in this article):
- VLOOKUP is a single formula that does all the lookup-and-fetch, but with INDEX/MATCH, you need to use both the functions in the formula.
- INDEX/MATCH can more advanced lookup – such as lookup to the left of the dataset, make row/column value dynamic, etc.
- INDEX/MATCH provides a lot more flexibility, which is better suited when building data models or working with complex data
You May Also Like the Following VLOOKUP Tutorials:
- How to Use VLOOKUP with Multiple Criteria.
- How to make VLOOKUP Case Sensitive.
- Use IFERROR with VLOOKUP to Get Rid of #N/A Errors.
- Use VLookup to Get the Last Number in a List in Excel.
- Excel Index Match
Useful Excel Resources: