Here are some of the queries they had about Index/Match formulas:
- 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? (we will find out soon, keep reading)
I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH.
Here in this blog post, I will try and compare these 2 formulas as objectively as I can (Disclaimer: I do have a penchant for the INDEX/MATCH combo).
VLOOKUP takes this hands down. For some people, Excel formulas are synonymous with VLOOKUP. For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users.
Verdict: VLOOKUP is a clear winner on popularity.
Ease of Use
VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. INDEX & MATCH are 2 separate functions that take 3 arguments each and should be combined to do a lookup. While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP.
Verdict: VLOOKUP gets the point for it ease of use.
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.
INDEX-MATCH combo can address all these issues. It can lookup and return a value which is to the left of the lookup value, and it can handle both horizontally and/or vertically structured data.
Also, since we are using MATCH to find the position of a column, it does not matter if you add or delete a column. The MATCH part of the formula would automatically take care of the position.
Verdict: INDEX/MATCH combo gives you a lot more flexibility than VLOOKUP.
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.”
That says it all!
Verdict: If speed is what you are looking for, INDEX/MATCH combo is the way to go.
Overall Verdict (Vlookup Vs Index/Match)
INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula. If you haven't already adopted INDEX/MATCH as the way of life, it is about time you do it.
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.
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.
Useful Excel Resources: