VLOOKUP vs INDEX/MATCH: The Hottest Debate in Excel

Today I had to formally weigh in on the debate between VLOOKUP and INDEX/MATCH. This argument has gone on for years, and last week I heard strains of it rising up again. So, here's what I say:

VLOOKUP is a tool. INDEX/MATCH is a tool. VLOOKUP has weaknesses, e.g., it's easy to break, and it can't do lookups to the left. Conversely, INDEX/MATCH is undoubtedly more robust. But it's actually a function nested inside another function. That makes it awkward to work with.

They're both legitimate choices. The problem comes up with the INDEX/MATCH zealots who call for the Excel developers at Microsoft to eliminate VLOOKUP. They are proud to post things like, "I haven't used VLOOKUP in 10 years" and insist that VLOOKUP shouldn't be taught to beginners because it's a bad habit.

Basically, they've turned INDEX/MATCH into a religion ... and they're viciously recruiting new members.

My video makes 3 points:
1. VLOOKUP and INDEX/MATCH are tools. Period.
2. Your own context dictates which tool to use.
3. Don't listen to the INDEX/MATCH insurgents. BOOO!

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:

My book: Guerrilla Data Analysis 2nd Edition

#VLOOKUP
#INDEXMATCH
#ExcelFormulas

[mathteacher]

"This is not a tool conversation, this is a situation conversation." - ok, I'm going to quote that at my next staff meeting if you don't mind. :)

[marcosbrooksallen]

Excellent Contribution brother Ozz. I totally agree with you, both functions are very efficient. However, in my humble experience, I have learned that in files of 2MB and up, the process of recalculation in the use of VLOOKUP is slower than using INDEX + MATCH and on the other hand, the formula of VLOOKUP weighs more (file size ) than the INDEX + MAX formula.

Without another particular, both functions are very good. I agree with you about the situations and contexts in which we find ourselves. Our decision to use one or the other will depend on it.

I reiterate, very good contribution brother Ozz!

[jasonm]

Come over to the INDEX/MATCH dark side. You don't know the power.

[AlessandroTrovato]

Thank Oz ! Great video! The context analisys is very important ! We have a great oportunity do make videos about this. How identify correctly the context before doing data analisys? Maybe a new playlist for you. A hug to you and have a nice week!

[ejboutin]

This video helped me do an index/match for the first time (even though it was a compare/contrast video). Thank you.

[theVsign]

Thanks for explaining the difference in such a clear way. I finally get it! Love this video.

[alexkim]

Remains my favourite video in this channel

[ExactProBi]

Could not agree more! Context is most important! Also, you can always use MATCH Function to select columns number dynamically even if the order of columns changes! In Financial data (structured data), VLOOKUP does the job almost 99% of the time!

[JR-lybx]

Your videos are an adventure, no telling where you will take us.

[dustymandan]

Ima start training my Excel classes w/ a blender, cutting board/knife, food processor & cutting stick! :D

[syedtalhamanzar]

I still know some thing an year before some one ask me hey U know advanced excel I ask me what is advanced excel he just simply reply to me how to use Vlookup and Hlookup. Both functions have its own uniqueness when I am working with the simple data set I prefer to use Vlookup where I only have to search forward in the table but when I am working on a larger data tables and I have to search both forward and backward I prefer to use Index match function. Well thank you for the video OZ :)

[wesszep]

Nice Oz, great context :) I don't use index match usually coz there too much formulae, but thanks for the example

[massms]

I always thought you're an INDEX/MATCH guy!
Context matters, but I can't see one where I have to use VLOOKUP.

[AkaExcel]

Oz, Thank you for your video. But in context of processing speed(much data) which is faster INDEX&MATCH or VLOOKUP?

[eladiobardelli]

I think about contex on related about fórmulas Dax, saludos from Chile

[josebetancourt]

Interesting video. Oz is right, its about the context of the situation. I beleive that the time will tell which method is "more used". But now, that the big data is right next door, other tools (eg. Get & Transforn) will increase in popularity and traditional formula tables will decrease. Oz can tell more about that. 👍

Thanks!

[lmk]

I use Index but yeah it's not always necessary if you have data on the left

[ExcelInstructor]

3:23 actually it can if in the array argument ull create reversed data array with choose for example and reverse the column order. Also, u can use Match with vlookup to make the formula resilient to column order change.

[christophocles]

It wasn't your intent, but you make a compelling argument that index/match is superior. I don't have to fix the lookup formula when I move a column. I can lookup to the left. When you duplicated a column on the right just to be able to use vlookup that made me eye-twitch. No, just no.

It doesn't take much more time to use index/match and it can do everything vlookup can do, _and more_. I agree with you that vlookup is easier, and it's good for quick and dirty stuff where you're just going to wipe out the formula and paste it into an email. Beyond that, it's better to plan ahead for changing needs and use index/match to begin with. Different tools in different contexts.

[mwaltercpa]

Offset(match... Anyone? I read once it was more efficient... a long time ago... Now it's just habit lol