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)
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)
Transcript of the video:
Learn Excel for 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 MrExcel netcast, I'm Bill Jelen.
I've done this video before, it's one of my favorite tricks.
If your VLOOKUPs are taking 30, 40, 50 seconds, four minutes, you know, anything, you're going to love this video; if your VLOOKUPs take one second, just click next and go on to the next video.
I have a VLOOKUP here, it's looking into a table of 115,000 items, doing 7000 VLOOKUPs.
So we're going to use some Charles Williams from Fast Excel code, to see how long it takes to do this VLOOKUP.
Alright, 4.09 seconds-- that's the typical VLOOKUP with ,FALSE at the end.
And this all came up because long, long ago, I was baited by some guy on Twitter who said that it would be better if you would sort your lookup table ascending, and I said, "No, that's not true at all, it doesn't matter whether we go ascending 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-- 4.84 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 50, you know what your top 50 best selling items are-- bring those to the top of the list and watch that-- 4 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 B, talking about VLOOKUPS, and over in Room A, guess who was sitting in that room-- it was Charles Williams.
Alright.
And Charles, hears 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 tenfold improvement." Now, Charles --Charles-- has the service of Fast Excel.
Alright, decision model's limited where, in half a day, he will analyze your workbook, and, he claims, make it a hundred times faster.
Right?
He'll find the bottlenecks.
And Charles comes to me and he says, "Look, that ,FALSE that you and your accountant friends are doing, it is the slowest thing in Excel.
If you would do a ,TRUE, it's a thousand times faster." And then-- Charles says this next clause as if it doesn't really matter-- he says, "Now, sometimes, it's wrong." And I went, "Oh, wait, Charles, you don't understand.
In accounting, 'sometimes it's wrong', is a non-starter; we do not accept sometimes it's wrong." And the time that it's wrong, the ,TRUE-- when you're doing a ,TRUE-- is we go look for a P32211 and it's not found, they're going to give you the item just less.
Alright?
And they're not going to tell you "we couldn't find it", they're just going to-- they're just going to-- give you the item that's less-- that's unacceptable.
And Charles says, "Well, here's what we could do: Imagine if you did a VLOOKUP of P32211 into just Column G, ask for the first Column, ,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 IF(VLOOKUP(A2,Table,1,TRUE)=A2, a VLOOKUP of comma 1, into the Table, see if it's equal to A2.
If it is, then it's safe to go on and do the second VLOOKUP; otherwise, they're not found.
I said, "Charles, do you realize I'm doing 7,000 VLOOKUPs, and now you're going to be doing 7 14,000 VLOOKUPs and 7,000 IF statements." I said, "You really think this is going to be faster?" Charles said, "Well, I'll bet you a pint." Alright, so here we go.
Remember the VLOOKUP that all of us are doing with the ,FALSE, 4.09 seconds; the Sort by Popularity, which is kind of hard to do, 0.3 seconds.
Here we go.
Here's Charles Williams, are you ready?
Look at that-- .04, four-one-hundredths of a second.
From 4 seconds down to four-one-hundredths of a second.
Imagine if you had a spreadsheet that was taking 40 seconds to calculate, and how much faster it 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.
Alright, so recap: VLOOKUP, when used with false, it's a slow function; sorting the data A to Z 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 item's not found; so we're actually going to do two VLOOKUPS-- lookup A2 in the column 1 at the table, and see if what we get back is a 2, if it is, it's safe to go do the VLOOKUP in the comma Column 2, otherwise, have an IF statement that says not found.
Alright.
Well, hey, thanks to Charles Williams for teaching me that amazing trick, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel
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 MrExcel netcast, I'm Bill Jelen.
I've done this video before, it's one of my favorite tricks.
If your VLOOKUPs are taking 30, 40, 50 seconds, four minutes, you know, anything, you're going to love this video; if your VLOOKUPs take one second, just click next and go on to the next video.
I have a VLOOKUP here, it's looking into a table of 115,000 items, doing 7000 VLOOKUPs.
So we're going to use some Charles Williams from Fast Excel code, to see how long it takes to do this VLOOKUP.
Alright, 4.09 seconds-- that's the typical VLOOKUP with ,FALSE at the end.
And this all came up because long, long ago, I was baited by some guy on Twitter who said that it would be better if you would sort your lookup table ascending, and I said, "No, that's not true at all, it doesn't matter whether we go ascending 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-- 4.84 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 50, you know what your top 50 best selling items are-- bring those to the top of the list and watch that-- 4 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 B, talking about VLOOKUPS, and over in Room A, guess who was sitting in that room-- it was Charles Williams.
Alright.
And Charles, hears 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 tenfold improvement." Now, Charles --Charles-- has the service of Fast Excel.
Alright, decision model's limited where, in half a day, he will analyze your workbook, and, he claims, make it a hundred times faster.
Right?
He'll find the bottlenecks.
And Charles comes to me and he says, "Look, that ,FALSE that you and your accountant friends are doing, it is the slowest thing in Excel.
If you would do a ,TRUE, it's a thousand times faster." And then-- Charles says this next clause as if it doesn't really matter-- he says, "Now, sometimes, it's wrong." And I went, "Oh, wait, Charles, you don't understand.
In accounting, 'sometimes it's wrong', is a non-starter; we do not accept sometimes it's wrong." And the time that it's wrong, the ,TRUE-- when you're doing a ,TRUE-- is we go look for a P32211 and it's not found, they're going to give you the item just less.
Alright?
And they're not going to tell you "we couldn't find it", they're just going to-- they're just going to-- give you the item that's less-- that's unacceptable.
And Charles says, "Well, here's what we could do: Imagine if you did a VLOOKUP of P32211 into just Column G, ask for the first Column, ,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 IF(VLOOKUP(A2,Table,1,TRUE)=A2, a VLOOKUP of comma 1, into the Table, see if it's equal to A2.
If it is, then it's safe to go on and do the second VLOOKUP; otherwise, they're not found.
I said, "Charles, do you realize I'm doing 7,000 VLOOKUPs, and now you're going to be doing 7 14,000 VLOOKUPs and 7,000 IF statements." I said, "You really think this is going to be faster?" Charles said, "Well, I'll bet you a pint." Alright, so here we go.
Remember the VLOOKUP that all of us are doing with the ,FALSE, 4.09 seconds; the Sort by Popularity, which is kind of hard to do, 0.3 seconds.
Here we go.
Here's Charles Williams, are you ready?
Look at that-- .04, four-one-hundredths of a second.
From 4 seconds down to four-one-hundredths of a second.
Imagine if you had a spreadsheet that was taking 40 seconds to calculate, and how much faster it 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.
Alright, so recap: VLOOKUP, when used with false, it's a slow function; sorting the data A to Z 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 item's not found; so we're actually going to do two VLOOKUPS-- lookup A2 in the column 1 at the table, and see if what we get back is a 2, if it is, it's safe to go do the VLOOKUP in the comma Column 2, otherwise, have an IF statement that says not found.
Alright.
Well, hey, thanks to Charles Williams for teaching me that amazing trick, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel
