The anti-VLOOKUP people are at it again, and let’s blame Sumit Bansal at TrumpExcel.com (of course, I’m joking). VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here! is a blogpost that’s quite thoughtful in how it addresses something in the Excel community that gets folks worked up.
Here come the anti-VLOOKUP scalawags.
If you see them, calmly close your spreadsheet and just let them pass.
Not even “VBA vs. Formulas” gets people so worked up. But, rather than end the debate, Sumit incited the anti-VLOOKUP people. (See the image to the right.) They call VLOOKUP worthless. They point out its weaknesses and some thump their chests and announce, “I haven’t used VLOOKUP since September 3rd, 1999!” Like it was the day they were released from prison and denounced their criminal past.
The Argument: VLOOKUP or INDEX/MATCH
In summary: VLOOKUP is a function that’s used to retrieve data from a source location. An alternative is to combine two functions INDEX and MATCH to do what VLOOKUP does however, INDEX/MATCH is more flexible and isn’t the memory hog that VLOOKUP can be.
Sumit does an excellent job of comparing the strengths and weaknesses of both VLOOKUP and INDEX/MATCH. The blogpost is definitely worth a read.
My Perspective On the Debate
There is no disagreement that INDEX/MATCH brings more horsepower than VLOOKUP, and there are times when INDEX/MATCH is the clear choice for getting something done.
The problem with INDEX/MATCH is that you have two functions, one nested inside the other, and both functions have their own syntax. This can be tough for a beginner to understand that we’re substituting one of the INDEX arguments with a MATCH function.
Regarding the memory and speed concerns, you’ve got to have many hundreds of VLOOKUPs before you notice a slowdown.
Why does this debate keep coming up, and with such fervor?
One possibility is something that Christopher Penn warns against in his blogpost Avoiding Magic for Magicians.
Basically, when experts in any field talk among themselves they can start to try to impress each other and lose sight of the needs of the layperson. Nuances, technical minutiae, what-ifs and yeah-buts take over the conversation, and eventually the tool is being fetishized instead of being discussed as an aid in service of a task.
Here’s how Chris describes it:
If you get trapped inside the magician’s fishbowl, your magic changes to be more about deceiving other magicians, magicians who already know the majority of the basic tricks.
Your tricks get increasingly complicated and complex, and as a result, more entertaining to magicians who understand the different levels of skill needed to perform the tricks. Paradoxically, your tricks get less and less entertaining to the general public, who can’t tell the difference between an Olram subtlety and a monkey shuffling cards.
3 Tasks For VLOOKUP or INDEX/MATCH
First, a question.
Question: You’re at home and want to hang a picture. Do you need a drill or a hammer?
Answer: Neither. You don’t need either. You need a way to get the picture to stay up on the wall.
I could come help you mix up some cement and permanently affix the picture to the wall. We could glue it to the wall, rivet it, or tape it. We could also find someone who’ll fetishize a particular drill as The Right and Only Drill. I’d rather focus on the task.
Here are 3 examples of tasks and my suggestion for completing each one.
(Below is a Microsoft Sway.
Use the arrows to scroll left-right.
Click the images to cycle through the stack.
To see the images full-size, click the white arrows in the lower right of the window.)
CONCLUSION: The False Dichotomy
I worry that this debate confuses beginners who are already intimidated by Excel and data. A retailer who wants to match 60 SKUs from a list of 200 Items is fine using VLOOKUP. He doesn’t need to worry about spreadsheet memory, speed or how non-robust VLOOKUP is. One alternative is the retailer not doing anything because he dreads sitting down and completing the task manually.
And that’s the false dichotomy: VLOOKUP or INDEX/MATCH
The real choices are: VLOOKUP or INDEX/MATCH or do it manually or hire a temp or indefinitely put the task off until “one day”
From that perspective, VLOOKUP is digital ecstasy. We need to lighten up and remember that these are all just tools that are in service of an objective. Satisfy the objective.
Angry Crowd image by Benjamin-The-Fox
I tend to use Vlookup as often as I can because in the corporate world that is what everyone is taught to use and understand. If I start throwing Index/Match combinations into the mix, I am bound to receive some confused stares and questions. It’s just more efficient for me to write functions with Vlookup since the majority of my peers are more than capable of instantly understanding what my formulas are doing (less explaining for me!).
I touch on this briefly in the last section (entitled “An Analytic Standard”) of a post I wrote on the Vlookup function: http://www.thespreadsheetguru.com/vlookup
Love the article.. You make me look like a monster bashing up the innocent Vlookup.. Just trying to call a spade a spade 😉 Jokes apart, Vlookup is a super popular function with immense utility. On this whole debate of Vlookup & Index/Match, one of my readers nicely summarized – “A nail needs a hammer and a screw a screwdriver”.. Whatever satisfies the objective 🙂
Sumit Bansal No no. You’re no monster. LOL! I appreciate what you do. I’m glad you wrote this article and generated the lively comments at the end of your blogpost. We needed this.
Chris Macro Thanks for the comment and sharing your blogpost.
You raise a very interesting perspective: people who need to be able to follow what you did in a spreadsheet. I was surprised a few years ago when I started meeting people who don’t develop spreadsheets but have to understand spreadsheets that are given to them.
It’s courteous to consider those folks during our development.
VLOOKUP always seems to be a significant threshold function that Excel users cross. Even if they’re doing it paint-by-numbers style it’s new territory until they do get to INDEX/MATCH and more complex techniques.
I posted some links on Sumit’s blog post, which, IMHO, should accompany such arguments as this. We’ve seen this same question come up for years and they’re good reads while on the subject.
For me it’s about choosing what is right for the situation. While I tend to not use VLOOKUP, generally it’s because I don’t trust users not to screw with inserting/deleting/changing columns. Of course they both have their merits, I wouldn’t say one is better than the other, just different tools in the tool bag. So while I generally use INDEX, I’m not anti-VLOOKUP either. 🙂
If we’re talking about what function a beginning user would choose, I think the obvious choice is VLOOKUP. Not only because of it’s simplicity, but because of the “ah-ha!” moments you see when users find the power there.
Zack Barresse Yes! you paint more context around the discussion and the answer to “which is better or worse is “it depends.”
That VLOOKUP “a-ha!” moment for beginners is a big deal. They deserve that. And you bring up another interesting point: building a spreadsheet for someone else vs. a one-time comparison of lists. So many things have to be considered in our tasks and it’s good to know that Excel provides so many tools.
Great post, Oz.
Re this: Basically, when experts in any field talk among
themselves they can start to try to impress each other and lose sight of
the needs of the layperson. Nuances, technical minutiae, what-ifs and
yeah-buts take over the conversation, and eventually the tool is being
fetishized instead of being discussed as an aid in service of a task.
…it reminds me of one of my own ‘pet peeves’ when a user posts a problem on a help forum with the title “Can someone help me with this complicated SUMPRODUCT” and so all the answers are variations on complicated SUMPRODUCT functions, when a mere PivotTable would answer the op’s question with ease.
So users themselves aren’t immune.
Jeff Weir that’s an interesting addition to the conversation. No one is immune. I guess we’ve gotta help each other remember the task.
Nice post! I’m with Chris and Oz. I think VLOOKUP make sense as a starting point for many people, and it’s widely used in the corporate world. INDEX / MATCH requires more skill and understanding. Yes, it’s a more powerful tool in the end, but IMHO it makes more sense to learn when you start running into VLOOKUP limitations