Why the vlookup formula is so commonly used is understandable;it has been around for a long time, it’s easy to use, and it’s so useful!
However, this is a bit like the story of the young elephant. Have you heard it?
The simple fable is that of a growing elephant. Did you know that while very young it can be held by simply a rope and a peg in the ground? Yes indeed; of course the elephant learns that it is not powerful enough to rip the peg out of the ground, so it stops searching for solutions. However as it matures into a glorious fully grown elephant, it has the strength to rip trees out of the ground, let alone a flimsy peg!
And yet, the learned behaviour sticks; and that mighty beast can be constrained by a simple peg and rope.
That my friends, is a lot like you and the VLOOKUP. There is a more powerful, more flexible formula to use that is even simpler; but most people out there aren’t using it!
But not you…not anymore.
Let’s start with what it does
For example: If I want to find what item in the table pertains the position 3, VLOOKUP will do that for me.
(It’s orange by the way)
But wait; where did the purple and green cell come from? Why do we need them?
Of course; you need to explicitly tell Excel where you want it to look (red box), and what column the answer should come from (it assumes the input is matched against the left most column)
Good, ok, so wheres the problem?
Glad you asked.
What happens when you insert a row?
Reason (1): Not robust
Stumped.
That’s right I used Clipart!
But Kyle, you say – Clipart hasn’t been around since the early 2000s!?
That’s right, its about as archaic as VLOOKUP.
Point proven?
Yes, yes, you can use a match formula in there to dynamically update the purple cell. But why overcomplicate it?
Reason (2): Slow to compute
Let’s insert some more columns and increase that table size now. And run trace dependents (alt, m , d) – those are the blue lines to show you which cell depends on those cells for its calculation.
You can see that the one formula references the entire table. That’s more storage size for Excel.
But Kyle, you say – computers are quick, surely they can process that at lightspeed?
More or less they can, but have lots and lots of these puppies and they will slow your Excel down.
But I’m not only talking so much about your computer being slow to compute; its you! What happens when you want to check it?
Reason (3): Highly not transparent
If you want to check this formula, do you really want to be counting across the columns to say, oh yes is it column 7 or 8 it should be on?
Didn’t think so.
The solution: the highly acclaimed LOOKUP formula.
This formula leaps tall buildings in a single bound… not really but it’s (1) more robust, (2) faster (3) more trans
parent and (4) simpler and you don’t need both HLOOKUP and VLOOKUP, just LOOKUP (less arguments).
Let’s take a look what happens when we use the marvellous LOOKUP formula to achieve the same thing.
You see? Just define your lookup vector (red box) and your result vector (purple box). No need to define all the area between your lookup and result vectors. And therefore no need to tell it which column (or row if its HLOOKUP) you need.
And now, everyone’s happy.
In summary; the VLOOKUP and HLOOKUP formulas are like LOOKUP’s much older, and uglier cousins, that have locked LOOKUP in the basement and feed it scraps under the door.
I’m mixing my metaphors now, but that elephant in the basement is angry and it has just realised it has the power!
Got more reasons? Have another perspective? Leave them in the comments section.
Want to learn more about how the LOOKUP formula works? When to use it versus an INDEX(MATCH) or SUMIF? More articles coming soon!
