VLOOKUP vs INDEX-MATCH? 28 experts share their opinions
This has got to be one of the oldest, and yet hottest topic among the Microsoft Excel’s community.
Currently there are about 1.43 million search results in Google just on this topic alone.
And so, DefeatExcel has reached out to seek the opinions of Excel experts such as Microsoft MVPs and Excel community experts so that we can hear their views right here in a single article.
Summary
- Why VLOOKUP vs INDEX-MATCH?
- Bill Jelen
- Ken Puls
- Purna R. Duggirala (Chandoo)
- Sumit Bansal
- Oz du Soleil
- Jon Acampora
- Mynda Treacy
- Tom Urtis
- John Michaloudis
- Brad Edgar
- Kasper Langmann
- Jeff Lenning
- Patricia McCarthy
- Kevin Lehrbass
- Ben Currier
- Rick Grantham
- Niels Weterings
- Liam Bastick
- Gašper Kamenšek
-
Charles Williams
- Onur Yilmaz
- Annie Cushing
- Jon Peltier
- Jon Wittwer
- Jacob Hilderbrand
- Jan Karel Pieterse
- Charley Kyd
- Chris Chua
No Time to Read?
Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.
But before that, why is there so much interest among this single topic?
Shouldn’t there be a clear winner since this topic has been discussed countless of times?
Not quite.
You see, whether VLOOKUP or INDEX-MATCH is better depends largely on how you define “better”.
“Better” can mean different things to different people. “Better” can also change in different situations. What is better depends on the circumstances.
A T-shirt can be better for a vacation while a white shirt can be better for a formal event.
So who do we turn to for advice then?
Yes, today we will turn to these highly skilled Excel mages who have been sparing with Excel monsters for a very long time.
They may come from all over the world, but they have a common characteristic – they are all mad about Excel.
Otherwise, why would so many adults come together to talk about this single topic?
I asked each of them (in no particular order) the following question:
VLOOKUP vs INDEX-MATCH: which is better?
- Microsoft MVP since 2005
It does not matter that INDEX-MATCH is more flexible than VLOOKUP.
If you find 100 people who know and use VLOOKUP, only 3 percent of those people will be familiar with INDEX-MATCH.
My goal is to build spreadsheets that others can understand. Thus, VLOOKUP wins for me.
The one exception: If I have to do 12 columns of VLOOKUP (Jan, Feb, Mar, … Dec) – then I suggest using one MATCH and 12 INDEX formulae.
- Microsoft MVP since 2006
VLOOKUP is the most important function to learn in Excel.
It has everything that Excel can throw at you for parameters including ranges, values, Boolean logic and defaults that are wrong 99% of the time. If a user can master this function, they can unlock any function in Excel.
Having said that, INDEX-MATCH is more robust and faster, but is a lot easier to learn for those who have mastered VLOOKUP than those coming to INDEX-MATCH without that background knowledge.
- Microsoft MVP since 2009
My views:
- For small data sets (few tens of thousands or less) it doesn’t matter which one you use. Go with what you like best.
- For larger sets of data see if you can analyze data with pivot tables or power pivot with data model capabilities.
- If you must use formulas for such large data sets, then use MATCH to get a pointer to the row where all the info you need and use INDEX to fetch the data.
- Microsoft MVP since 2014
I am an INDEX-MATCH loyalist and prefer it over VLOOKUP.
The powerful combination of INDEX-MATCH allows me to do a lot more than VLOOKUP (such a looking to the left of the lookup value).
While I prefer INDEX-MATCH, in my online and in-person trainings, I always educate people about VLOOKUP first. VLOOKUP is easier to learn and far more popular.
- Microsoft MVP since 2015
This has become the Excel community’s version of political debates. Lots of talk, lots of evidence, lots of facts, very little persuasion.
The merits of INDEX-MATCH over VLOOKUP have been established. However, for my usual purpose, the merits are irrelevant. A scenario:
- A range of 50 rows and 5 columns of data that need to retrieve data from a lookup-range that’s 200 rows and 6 columns.
- After I’ve retrieved the data I’m going to Copy/Paste-as-Values. Nothing is dynamic.
In that case, I’m using VLOOKUP and skipping the syntax and jungle of parentheses with INDEX-MATCH.
Also, with the introduction of Get & Transform, I might skip the VLOOKUP and use a Left Outer Join.
In my work, INDEX-MATCH-MATCH has proven far more valuable than INDEX-MATCH.
Bottom line for me: these are just tools and I’m looking to get a task done.
If there is a fly in my home, I’ll smash it with a newspaper, a shoe, or a paper bag. I’m not going to drive to the store to get proper fly poison. What’s the goal? A dead fly.
VLOOKUP kills flies!
- Microsoft MVP since 2014
I love VLOOKUP, but INDEX-MATCH is a better solution because it is less error prone. There are two major benefits of INDEX-MATCH over VLOOKUP:
- INDEX-MATCH allows you to insert columns between the lookup column and the return column without having to modify the formula.
- We can return a value in a column to the LEFT of the lookup column with INDEX-MATCH. This cannot be done with a standard VLOOKUP formula.
I explain these differences in more detail in video #3 of my free video training series on Lookup Formulas.
- Microsoft MVP since 2006
I don’t think one is better than the other.
VLOOKUP is a great first function to learn. It’s relatively easy to understand and very powerful. For these reasons it will always be a great function.
INDEX & MATCH is able to overcome some of the limitations of VLOOKUP, particularly looking up to the left, but it’s more complex and can be daunting to beginners.
I always recommend people learn INDEX & MATCH once they have mastered VLOOKUP.
- Microsoft MVP since 2008
It depends on what you are looking up, what you expect the formula to return, your familiarity with Excel’s nested functions, and if you care about tiny differences in ex ecution times (INDEX and MATCH are faster), that can become noticeable in very large calculation projects.
VLOOKUP’s syntax is a single-function construction with four arguments (one is optional), and it’s a popular function for the world’s millions of Excel users who need a basic lookup tool for their work. VLOOKUP operates well in either direction: lookup value at left returning data at right, or vice versa with a nested array constant or CHOOSE function. VBA’s VLOOKUP worksheet function is intuitive for programmers. VLOOKUP and HLOOKUP are useful functions for basic lookup situations.
Combining the INDEX and MATCH functions provides you with everything VLOOKUP does, and more. Lookups can be much more complex than for generic text. Looking up values by case sensitivity; character count; first or last numeric value; positive or negative numbers; absolute values; partial strings; row and column header labels; the options are enormous with INDEX and MATCH that VLOOKUP and HLOOKUP do not handle efficiently, if at all. INDEX with MATCH provides a more versatile, straightforward construction syntax than VLOOKUP for range reference edits and complex lookups. To be fair, understanding INDEX with MATCH takes more time for some users to grasp than VLOOKUP, but the value of this function combination for diverse lookup scenarios cannot be understated, and is, in my opinion, the better choice.
MyExcelOnline.com
INDEX-MATCH is way better as you can look to the left of your data, something that is not possible using VLOOKUP!
Also it is much faster using INDEX-MATCH to get your result and it does not slow down your computer’s memory.
BradEdgar.com
I would have to say that both the INDEX-MATCH and the VLOOKUP formula both have their benefits. It depends on the data sets that I’m contending with and whether I need to do a
multiple criteria lookup.
If I need to do a multiple criteria lookup, I’ll use an INDEX-MATCH array formula to get my results (see article).
I would also use the INDEX-MATCH function out of necessity if on the data setup that I’m trying to lookup the value from, my returning column is located to the left of my lookup range.
Finally, if I have a standard lookup that is required where the returning column is located to the right of the lookup column, hands down I use the VLOOKUP formula because of it’s ease of use.
Spreadsheeto.com
While VLOOKUP definitely is much easier to learn, there’s really no doubt about it. INDEX-MATCH is the most powerful lookup tool in Excel because it’s so flexible and solid. It even adapts to most structural changes in the sheet. Use it for all your lookup needs, even multi-criteria lookups.
Excel-University.com
For me, it depends on the workbook.
If the value I’m trying to return is a number, I use SUMIFS. I know that wasn’t one of the choices and that it is not a traditional lookup function. But it has big benefits, such as it matches equivalent values even when stored as different data types, supports multiple conditions, and returns zero instead of an error when no matches are found.
If the value I’m trying to return is a text string, I’ll probably use INDEX-MATCH for recurring use workbooks that will be updated each period because it is less likely to break when new columns are inserted.
Or if this is a one-time workbook or the structure is unlikely to be updated, I’ll break out VLOOKUP. But, if the data contains no duplicates and contains a numeric key column, I’ll use SUMIFS as the first argument of a VLOOKUP.
Rowdy, I know. Next time you are faced with a lookup task, and the return value is numeric, give SUMIFS a try, it makes an amazing lookup function.
Excel-University.com
True story. My husband and I spent an entire evening arguing over which was better – VLOOKUP or INDEX-MATCH.
I championed the VLOOKUP and he the other. His big argument was that you didn’t need to sort your data using INDEX-MATCH while you needed to with VLOOKUP.
I personally like VLOOKUP better simply because I find it more intuitive and easier for people to understand how it works.
What many people fail to realize is that if you include the range_lookup argument as FALSE then you don’t need to worry about sorting.
VLOOKUP is superior to INDEX-MATCH as long as you control your “table” and can arrange it so that the lookup column is where you want it.
Some people claim INDEX MATCH is faster and perhaps if you are looking through millions of records you might notice a slight difference.
So, my vote is for VLOOKUP, using the False range_lookup, and incorporating the IFERROR or IFNA function into it to clean it up a bit.
Now, I use VLOOKUP and my husband remains unconvinced and uses INDEX-MATCH.
No Time to Read?
Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.
Get The Defeat Excel Handbook Today!
50+ Frequently asked how-tos
No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.
PDF Format
Easily search through the book to find the solution you need. Plus it works offline too.
Effective solutions
No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.
MySpreadsheetLab.com
For occasional Excel users who have limited time and patience the VLOOKUP function might be the best option. It is easier to master a single function.
However, those that can invest just a little bit of time to understand how to combine the INDEX and MATCH functions will definitely benefit!
Why? If you learn INDEX-MATCH then you’ll never need to use a VLOOKUP or HLOOKUP (and for some reason the HLOOKUP annoys me!).
Let’s not forget that INDEX-MATCH can easily look to the left (VLOOKUP requires a complex trick to do this).
It’s often much more efficient (calculation time) to use INDEX-MATCH and in my experience less errors occur when using INDEX-MATCH as you are directly referencing the lookup and answer columns (no column index number is required). Hard coding the column index number in a VLOOKUP is dangerous!
Besides learning Pivot Tables, learning how to combine Excel’s INDEX and MATCH functions is one of the best things that you can learn in Excel.
ExcelExposure.com
In general, I prefer VLOOKUP when the data is properly setup for it with the lookup value on the left, but I will make exceptions when dealing with any spreadsheet that is to be used by anyone but myself.
INDEX-MATCH is a lot more powerful in terms of reliability, and allows for the ability to change your data layout by adding columns and such without it affecting your formula.
However, I’ve found that users who are not very skilled in Excel have a very difficult time with the formula, and are much more receptive to a VLOOKUP. Especially anything that will need to be reviewed by or explained to the higher ups will likely get a VLOOKUP even if it is not the ideal solution for the problem.
Excel.tv
When you are on top, everyone tries to take a shot at you. The VLOOKUP haters are in full force. They are relentless. INDEX-MATCH is faster. It’s sexier. it’s better. It makes a fluffier omelet. Blah, blah, blah.
Ugh.
To be clear, VLOOKUP haters have some good points. INDEX-MATCH is at least as fast as VLOOKUP, and likely faster if you have thousands of rows/columns. But if you have smaller data sets… meh… it’s not much of an issue. But if your large spreadsheets are grinding to a halt, then sure… take a look at replacing your VLOOKUPS and INDEX-MATCH.
Another plus for the INDEX-MATCH is its versatility. Need to LOOKUP to the Left? Want to delete columns from your VLOOKUP source? Errr… VLOOKUP isn’t the best for those scenarios. There are some workarounds, but who needs that kind of complication? Not me.
But remember… everybody knows how to do a VLOOKUP. Everybody.
You can pretty much guarantee that you can hand off your Excel model to just about anyone and they can read/understand your logic in the event something ever breaks or needs updating. Plus, it’s just so EASY. SIMPLE.
Jon Acampora over at Excel Campus compares VLOOKUP to ordering coffee from Starbucks. You go down the menu on the left until you find your Latte, then you go a few columns to the right until you find your size, then walah… there’s your price. Everyone can understand that, because it is how we are used to consuming columns of data in our everyday life.
My thoughts on it? Well, I’m a bit of an old-timer. I use VLOOKUP all day. It’s quick, easy and I know that anyone I hand my spreadsheet to can understand my logic. It’s a lowest common denominator kind of thing. But if my spreadsheets start slowing down, or I need to do more complex lookups (to the left, etc) then I opt for INDEX/MATCH.
So there you have it, first I go VLOOKUP and then INDEX/MATCH if needed.
Excel-Easy.com
Many people know how to use the VLOOKUP function in Excel, but few people know how to use the INDEX-MATCH function.
We recommend our visitors to master the VLOOKUP function first. It’s easier to understand. However, a drawback of the VLOOKUP function is that it can only look up values in the
leftmost column of a table.
Therefore, we teach our visitors how to use the INDEX-MATCH function to look up a value that is not in the leftmost column of a table.
- Microsoft MVP since 2012
I really don’t recommend VLOOKUP and HLOOKUP. As an accountant myself, I know this profession loves these functions but they don’t always work as expected.
LOOKUP, on the other hand, does have a useful function in the world of financial modelling.
I prefer the more versatile INDEX-MATCH, one of the most common and powerful function combinations in Excel. Indeed, INDEX-MATCH idea can even be extended.
- Microsoft MVP since 2016
If it’s a popularity contest, then VLOOKUP has won, since it is much more used that the INDEX-MATCH.
But if it’s a real contest, INDEX-MATCH combination takes the trophy home, since it can do things, VLOOKUP cannot, like return data to the left of the lookup column, or return answers that are not in the same “table” as the lookup column.
However, I quite fancy a VLOOKUP-MATCH combination for a “two-dimensional” LOOKUP.
- Microsoft MVP since 2009
The problem is not which is better but that they both have serious and well-known shortcomings.
VLOOKUP is simpler but INDEX-MATCH is more flexible but more complex to use.
FastExcel’s MEMLOOKUP and the the AVLOOKUP2-AMATCH2 family are faster, more powerful and often simpler to use.
For more information about MEMLOOKUP and AVLOOKUP2, see here.
- Winner, Excel Turkey Championship 2016
Both have advantages depending on your situation.
Personally, I make my decision based on the complexity and scope of the workbook I am working on. Writing VLOOKUP is quick and easy. If that’s a small study I can use VLOOKUP.
However, If I am creating a complex template or developing a large financial model then I prefer to use INDEX-MATCH. Because it is more reliable (works independently from column numbers), it is faster and more reliable.
I also use VLOOKUP-MATCH pair sometimes as well.
Annielytics.com
As I explain in this post, INDEX-MATCH is much more flexible than VLOOKUP. But, in my opinion, VLOOKUP is a little easier for neophytes to wrap their minds around.
- Microsoft MVP since 2001
I don’t use VLOOKUP.
When I first needed to look up something in an Excel range, I needed to find a value from a two-dimensional table, so I needed something more flexible.
I soon learned about INDEX(MATCH,MATCH), where one MATCH got me the right row and the other got me the right column. It is this added dimensionality, and the ability to look for a match in any row or column and not just the first, that make INDEX(MATCH) superior.
Vertex42.com
INDEX-MATCH is more powerful and flexible.
The main reason I use INDEX-MATCH instead of VLOOKUP is that VLOOKUP requires the lookup range to be on the left of the table.
The lookup_array in the MATCH function doesn’t even have to be in the same table or worksheet as the return array or reference in the INDEX function.
MATCH simply returns a number and you can use that number for the [row_num] or [column_num] or [area_num] or use two MATCH functions for a 2-D lookup or three MATCH functions for a 3-D lookup.
- Microsoft MVP since 2012
Vlookup is simpler, if your lookup column is on the left then I use that.
If not, then I use Index Match so I can get the match from any column.
- Microsoft MVP since 2002
VLOOKUP is better because it is easier to understand for beginner to intermediate Excel users.
INDEX-MATCH is better because it will continue to work if you insert or delete columns in the lookup table and allows the lookup column to be anywhere in the table. It can also be more efficient in case you need to lookup multiple columns of the same row, provided you place the MATCH in a separate column and refer to that MATCH result from each column having the INDEX function.
- Microsoft MVP 2005-2014
INDEX-MATCH is much better:
- It’s never slower than VLOOKUP and can be much faster.
- It returns a reference rather than a value, which allows us to use it for more purposes.
- It doesn’t care where the result array is with regard to the lookup array.
- It can return approximate matches from data sorted largest to smallest.
DefeatExcel.com
VLOOKUP gets the overall thumbs-up for me due to it being a single function. I value simplicity a lot and anyone looking at a VLOOKUP function will know what you are trying to do.
However if I’m creating a model for others, I may use a VLOOKUP-MATCH for a dynamic column reference.
Lastly, if you requires more firepower to take down larger Excel monsters, go for INDEX-MATCH for its flexibility and prowess.
Learning when to use VLOOKUP, VLOOKUP-MATCH, and INDEX-MATCH is also a skill itself!
Conclusion
Awesome! You made it to the end of this epic post where so many Excel MVPs and community experts come together to give you a holistic view of the VLOOKUP vs INDEX-MATCH debate.
I’ll like to thank all the experts above for setting aside their precious time to offer their sincere opinions. This post would not be possible without their help at all.
And if you’re based in Singapore, check out our series of Excel courses in Singapore to get yourself and your colleagues ahead today!
No Time to Read?
Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.
Get The Defeat Excel Handbook Today!
50+ Frequently asked how-tos
No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.
PDF Format
Easily search through the book to find the solution you need. Plus it works offline too.
Effective solutions
No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.