Feb 02, 2020

Using Vlookup? Don’t! Use THIS Instead

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


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!

Get Debt Repayment Calculations

Fet the .xlsx model which shows the four different calculation types

Popular Tutorials

NOV  20TH  2019

Calculating Debt

Whether you’re a company, a fund, or a project… it’s likely that you have to repay debt.

NOV  17TH  2019

Working Capital

Working capital seems to be an area of confusion in financial modeling and adjusting them so we get the cash item.

NOV  10TH  2019

De-risking Your Model

Whatever type of model you have in front of you – for Corporate Finance, Project...


Learn the Secrets of the World’s Top Financial Modelers

Get the behind-the-scenes top 15 Excel tips and shortcuts I use to build financial models known only by top financial modelers

Get Started

Learn the Secrets of the World’s Top Financial Modelers

Get the behind-the-scenes top 15 Excel tips and shortcuts I use to build financial models known only by top financial modelers