Faster VLOOKUP

September 20, 2017 - by Bill Jelen

If you have a large worksheet, a lot of VLOOKUPs can begin to slow things down. Do you have a slow worksheet due to VLOOKUP? I am talking a worksheet that requires 40 seconds or 4 minutes to calculate. In today's article an amazing formula with two VLOOKUPs using the range lookup will solve the problem.

VLOOKUP is a relatively expensive function. When you are looking for an exact match, Excel has to look through the lookup table one row at a time.

The workbook that I am using today is doing 7000 VLOOKUPs into a table of 116,000 items. On a really fast 64-bit machine with 8 cores, the recalc time is 3.01 seconds.

One way to improve VLOOKUP is to move the best-selling items to the top of the lookup table. Get a report of the top 100 best-selling items and move those items to the top of the list. Sorting by popularity improves the recalc time to 0.369 seconds. This is eight times faster than the first result.

But there is a way to speed things up even more. As you are building your VLOOKUP, when you get to the fourth argument to choose False, there is another option that is hardly ever used. Excel says “True” does an “approximate match.” This is not at all correct. If the Excel team were being honest, they would explain that True “provides a correct answer a lot of the time, but other times, without any warning, we are going to slip the wrong answer in there. I hope you don’t mind restating your numbers to the Securities and Exchange Commission.”

Sure, there is a proper time to use True. See this article. But it would be really bad to use True when you are trying to do an exact match.

If you try to use True for an exact match, you will get the right answer a lot of the time. But when the item you are looking for is not in the table, Excel will give you the value from a different row. This is the part that makes “True” a non-starter for everyone in Accounting. Close is never correct in Accounting.

Note

I learned the following trick from Charles Williams. He is the world’s foremost expert on worksheet speed. If you have a slow workbook, hire Charles Williams for a half day of consulting. He can find the bottlenecks and make your worksheet faster. Find Charles at http://www.decisionmodels.com.

While I and all accountants reject the “True” argument of VLOOKUP because of the unpredictability, Charles Williams argues for True. He points out the True is much faster than False. Hundreds of times faster. He concedes that sometimes you get the wrong answer. But he has a way to deal with the wrong answers.

Charles actually wants you to do two VLOOKUPs. First, do a VLOOKUP and return column 1 from the table. See if the result is what you were looking up in the first place. If that result matches, then you know it is safe to do the real VLOOKUP in order to return some other column from the table:

=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")

On the face of it, this seems insane. To use Charles’ method, you have to do twice as many VLOOKUPs. But, when you time the calculation time for this method, it is 35 times faster than the normal VLOOKUP.

Note that while most lookup tables do not have to be sorted, when you are using True as the fourth argument, the table does have to be sorted. For a 7-minute discussion of how the True version of VLOOKUP hops through the lookup table, see http://mrx.cl/TrueVLOOKUP.

Thanks to Charles Williams for teaching me this feature and to Scott St. Amant for nominating it for a top 40 tip.

Watch Video

VLOOKUP when used with False is a slow function
Sorting the data AZ does not speed up the function
Sorting by popularity could speed up the function
Switching to VLOOKUP with True is faster, but it will report the wrong answer if the item is not found
To mitigate the problem, do a VLOOKUP(A2,Table,1,True) to see if the result is A2 first
14000 VLOOKUP(True) and 7000 IF run faster than 7000 VLOOKUP(False)

Auto-Generated Transcript

Learn Excel from MrExcel Podcast
episode 2031 faster vlookup I'm
podcasting all of the tips in this book
click the I on the top right hand corner
to get to the watch list
hey welcome back to the mr. hutnik cast
I'm Bill Jelen I've done this this
video before it's one of my favorite
tricks if you're have the look if you
have vlookup star taking 30 40 50
seconds four minutes you know anything
you're gonna love this video if your
vlookup stick one second just click Next
and go on to the next video I I have a
vlookup here it's looking into a table
of 115,000 items doing 7000 vlookup so
we're gonna use some Charles Williams
from fast Excel code to see how long it
takes to do this vlookup alright four
point zero nine seconds that's the
typical vlookup with comma false at the
end and this all came up because long
long ago I was baited by some guy on
Twitter who said that would be better if
you would sort your lookup table a
sending I said no that's not true at all
it doesn't matter whether we go a
sending or descending or completely
random the vlookup just has to go look
from item to item to item and so when we
sort the table see it actually takes
longer four point eight four seconds so
you know it's not true that sorting the
the table will make it go faster but
really the thing that could make it go
faster if somehow you could sort by
popularity if you could get the best
selling items at the top of the list
even you know your top fifty you know
what your top 50 best selling items are
bring those to the top of the list and
watch that for seconds goes down to 0.36
seconds a tenfold improvement in time
using sort by popularity now hey a few
years ago I was lucky enough to be
invited to Amsterdam to present at an
Excel summit there and it's not like
most of my seminars where it's just me
right there were two tracks so room a
and room B and I was over in room be
talking about vlookups and over in room
a guess who was sitting in that room it
was Charles Williams alright and Charles
here's
his name being mentioned through the
wall so he comes over to watch it he
watches my little demo there where I go
from four seconds to 0.36 seconds he
comes up to me afterwards he says I bet
you're pretty happy with that
improvement
I say yeah that's a tent full
improvement now Charles Charles has the
service of fast Excel our decision model
the decision models limited we're in
half a day he will analyze your workbook
and he claims makes make it a hundred
times faster right he'll find the
bottlenecks Annette and Charles Charles
comes from he says look at comma false
that you and your accountant friends are
doing it is the slowest thing in Excel
if you would do a comma true it's a
thousand times faster and then Charles
says this next Clause is if it doesn't
really matter he says now sometimes it's
wrong oh wait Charles you don't
understand an accountant sometimes is
wrong is a non-starter we do not accept
sometimes it's wrong and and the time
that it's wrong the comma true when
you're doing a comma true is we go look
for a P 3 2 2 1 1 and it's not found
they're gonna give you the item just
less alright and they're not gonna tell
you we couldn't find it they're just
gonna they're just gonna give you Adam
just less that that's unacceptable and
Charles says well here's what we could
do imagine if you did a vlookup of P 3 2
2 1 1 into just column G ask for the
first column comma true and see if what
you get back is what you were looking
for if what you get back is what you're
looking for then you know it's safe to
go to the second vlookup if it's not
what you were looking for then you have
an if statement there that says not
found alright so we do a vlookup of a2
into the table comma 1 see if it's equal
to 82 if it is then it's safe to go on
and do the second vlookup otherwise they
not found I said Charles do you realize
I'm doing 7000 vlookups and now you're
gonna be doing 7 14000 vlookups and 7000
if statements I I said you really think
this is gonna be faster sure I said well
I'll bet you a pint alright so here we
go remember the vlookup that all of us
are doing with the comma falls 4.0 9
seconds the sort by popularity which is
kind of hard to do
Oh point three seconds here we go here's
Charles Williams are you ready look at
that point zero four four one hundredths
of a second from four seconds down to
four one hundreds of a second imagine if
you had a spreadsheet that was taking
for 40 seconds to calculate and how much
faster would be using this to vlookup
method it's an amazing trick and yeah I
guess I stole the trick and put it in
the book although I put a great cartoon
version of Charles Williams in there
saying he's the fastest guy in all of
Excel you can buy this tip and all of
the other tips in this book click that I
at the top right hand corner
all right so recap vlookup when used
with false it's a slow function sorting
the data a disease does not speed up the
function unless you sell a lot of things
to begin with a and B sorting by
popularity does it's about a tenfold
increase you could switch to vlookup
with true but it'll report the wrong
answer if the items not found so we're
actually gonna do two vlookups lookup a
two in the column one at the table and
see if it what we get back is a two if
it is it's safe to go do the vlookup in
the common column to otherwise have an
if statement it says not found all right
oh hey thanks to Charles Williams for
teaching me that amazing trick and
thanks to you for stopping by will see
you next time for another net cast from
MrExcel

Download File

Download the sample file here: Podcast2031.xlsm

Title Photo: Free-Photos / Pixabay