fbpx

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

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.

shirt

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?


Bill Jelen

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.


Ken Puls

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.


Purna R. Duggirala (Chandoo)

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.

Sumit Bansal

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.


Oz du Soleil

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!


Jon Acampora

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.


Mynda Treacy

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.


Tom Urtis

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


John Michaloudis

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.


Brad Edgar

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.


Kasper Langmann

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.


Jeff Lenning

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 🙂


Patricia McCarthy

Excel-Diva.blogspot.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.

Kevin Lehrbass

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.


Ben Currier

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.


Rick Grantham

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.


Niels Weterings

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.


Liam Bastick

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.


Gašper Kamenšek

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.


Charles Williams

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.


Onur Yilmaz

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.


Annie Cushing

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.


Jon Peltier

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.


Jon Wittwer

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.


Jacob Hilderbrand

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.


Jan Karel Pieterse

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.


Charley Kyd

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.

Wow! Are you still with us?

And lastly, my take for this question:

Chris Chua

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!


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.

14 thoughts on “VLOOKUP vs INDEX-MATCH? 28 experts share their opinions”

  1. Depend what you do. Vlookup is simple to use especially if you only look for one criteria. But however there is a limits on this formula.

    I prefer Index/Match if I look for more than 1 criteria. Especially if you work in Finance. Example Fund/Currency/Cusips etc.

    1. Hi Song, I think you’re right! It’s best if we know how to use both of them so that we can apply the most appropriate tool in the right situation. For multiple criteria, sometimes I turn to SUMPRODUCT. But as the tool gets upgraded, so is the complexity as well. A good Excel user should balance between the pros and cons to select the best tool.

  2. Is it not interesting, and almost laughable, that practically everyone who supposedly prefers VLOOKUP does so with exceptions and caveats, while those who prefer INDEX-MATCH do so unashamedly and without reservation? Even more amazing are those who claim to favor VLOOKUP because it is more popular or more widely known while simultaneously admitting that INDEX-MATCH is more robust and flexible. I would wager that some of those people make their living as consultants and need to dumb things down to make sure they keep their clients happy and feeling smart. Lots of things are more popular just because they are cheap or easy, but that does not make them better.

    Then there is my personal favorite, the ones who choose VLOOKUP as long as the data structure is small or convenient…which is why tens of thousands of uninformed people will spend minutes/hours every week repeatedly rearranging the data they receive from a source (over which they have no control) just for the “convenience” of a VLOOKUP. Many of them will even end up writing VBA code to do that repetitive rearrangement for them. Now tell me that’s not more complex than INDEX-MATCH! So if a convenient arrangement (looking up to the right) and a small amount of data are the clear reasons/excuses to use VLOOKUP, then try this one on for size. I have just 50 rows of data in 86 columns. You must write a lookup to return from column BN while matching on column T. If you did not instantly, in one second or less, know that column BN would need to be column number 47 in that scenario, then your VLOOKUP is a failure because my INDEX-MATCH was already written in the time it took you to go and count your columns.

    Please don’t get me wrong. Continuing to teach VLOOKUP because it is so ingrained in the history of Excel files and the psyche of millions of basic users is one thing. It is like those who have to be acquainted with COBOL because they are programmers for a banking or government institutions. But continuing to perpetuate the myth that using VLOOKUP is somehow ok (or even admitting it is only ok most of the time) does a disservice to the Excel community because it throws those poor souls to the wolves. They go about their lives happily using VLOOKUP when it is convenient. And because it is convenient most of the time, they never practice or learn to appreciate INDEX-MATCH, which is why they will spend inordinate amounts of time rearranging inconvenient data and adding helper calculations to make things compatible with an inferior function all because they never had any proper encouragement to do it a better way.

    And don’t even get me started on all the hype about XLOOKUP, which still can’t do everything you can accomplish with INDEX-MATCH and INDEX-AGGREGATE (for multiple matches).

    1. Hi David, that’s a great analysis! I agree with you that INDEX-MATCH is a much more superior setup than VLOOKUP or the new XLOOKUP. But in my experience of teaching others how to do Excel, there are some people who struggle with nested functions. I think Microsoft recognizes this as well with the introduction of XLOOKUP to cater to such group of people, while trying to “upgrade” the ancient VLOOKUP.

      1. I hear what you’re saying, but cell phone manufacturers, the makers of iOS/Android/Windows operating systems, and pretty much anything electronic don’t keep encouraging their customers to do things the way they’ve done for the last 15 years. Their customers either learn (and pay for) more complicated devices, or they get left in the dust. And I don’t hear 50% of the population complaining that people electronics consumers can’t possibly learn just one more thing and should keep flipping open their phones and using 8-3-3-9-9-8 to T-E-X-T their friends while they insert a VHS tape and push play to watch their favorite movie. (I use the 50% mark in that statement because it always feels like half the world favors or at least caters to VLOOKUP while the other half prefers INDEX-MATCH.) And where are the tens of thousands screaming that new cars don’t come with DVD players any longer, much less cassette tape players? That’s right, those people are a small minority and are pretty much shunned by the masses who want to play everything via Bluetooth from their mobile devices.

        So why do we treat the average Excel user differently? Are they so inferior and incapable of adaptation in Excel while at the same time constantly adapting to everything else in their lives? Maybe we should update the old saying and go with “you can’t teach an Excel user new tricks.” My biggest point is that we, the community of Excel power users, spend in inordinate amount of time accommodating the users of VLOOKUP, often at our own peril, instead of expecting them to keep up or get left behind. And as far as XLOOKUP being a means of catering to that population, I could not agree more, except that I weep for the many, many better things on which Microsoft could have spent that time and money.

        1. Your view is really interesting. I agree with you that because Excel usually has a few ways to get to the same desired results, we then mentally assigned different methods to different difficulty levels, and assume that the average Excel user will not be able to keep up and get left behind. I have no doubt that INDEX-MATCH is the most flexible method to perform a lookup, and the average Excel user should pick this up to expand his/her capabilities.

        2. You said exactly what I was thinking, David. I was actually surprised to read all of the preferences toward VLOOKUP from the power users, especially considering most of the reasons were along the lines of, “because this is how we do things”, and also came with all of the formatting caveats. I learned both functions simultaneously, and I overcame the challenge of a nested function simply by putting the function into spoken language: “Return if you find .” As time consuming as cleaning data already is, the hassle of reformatting tables to fit the VLOOKUP function is much more tedious than using an INDEX MATCH instead.

          1. Thanks Shelby, I think that’s a wonderful idea to first fill in the sentence “Return THIS if you find THAT THERE”.

    2. I agree many times over. After many years using of using V-LOOKUP I only discovered INDEX-MATCH by chance in a comment section by some random user like yourself that explained why it was better. Since I started using it I have never used a V-LOOKUP again.

      I pull a lot of table queries from my companies ERP system and any time I added a field I would have to go back and fix formulas for the V-LOOKUP to find the proper row. If I have manually data I often just convert it into tables now too because the INDEX-MATCH works even better in that format. I tell it what table-headers to search for and let it do it’s thing.

      It doesn’t take me more time to type in a INDEX-MATCH formula than it does to type in a V-LOOKUP formula so I use it for small data structure. So like you, I don’t understand how anyone could argue that V-LOOKUP is more convenient.

      I have noticed it’s more difficult to convert some of the people I work with to the INDEX-MATCH way of working but everyone who I have convinced all abandoned their V-LOOKUP.

      1. Indeed Craig.

        INDEX-MATCH not only replaces VLOOKUP and HLOOKUP and even the new XLOOKUP, but also allows users to look left (or simply look anywhere they want) without the hassle of hardcoding any column number. Great that you’ve found this treasure!

Leave a Comment

Your email address will not be published. Required fields are marked *

The Premium Data Analysis Course

Special Promo ends in

Days
Hours
Minutes
Seconds