VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here!

During one of my Excel Training sessions, I was surprised to find out that the people were aware and quite proficient in using VLOOKUP, but most of them had no idea about INDEX/MATCH.

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).

Popularity

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

Click here to download the FREE Ebook: The Ultimate Guide to Using Excel VLOOKUP Function 

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.

Vlookup Vs Index

Verdict: VLOOKUP gets the point for it ease of use.

Flexibility

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.

Vlookup Lookup Value on Right

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.

Speed

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.

Vlokup Vs Index Match Comparison

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:

Useful Excel Resources:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)