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