Every day, I see a tweet telling people to sort their VLOOKUP table A-Z. Does this really help? No! See a head-to-head comparison of unsorted versus sorted and then other ways to make your VLOOKUPs truly faster!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1790: VLOOKUP Does Sorting Help?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen. First, a shout-out to Debra Dalgleish.
Debra is a Excel MVP who monitors Twitter looking for people that she can help in Excel, and, in the course of that, she comes up with funny tweets about Excel and she posts those every Friday.
Just watch for the hashtag Fridayfun Excel tweets and they are hilarious.
I look forward to this every Friday and, sometimes, when I'm bored on my coffee break, I'll go out to Twitter and watch for interesting things to come on, just watching for Excel but, you know, there's a few things.
Willie Mays.
I don't know what Willie Mays knows about Excel.
I'm sure he can't do a VLOOKUP but this tweet gets retweeted all the time, and then the religious group, there's this about average and excel, that gets retweeted over and over and over, and as I'm cruising through looking for good Excel tweets, these come up, and then this one is posted every single day.
VLOOKUP make sure the first column is A-Z and you name the data you’re analyzing, and I see that one every day, and I'm like, what are they talking about, because, as far as I know, VLOOKUP, it's only important to have the first column sorted if you're using the , TRUE version, not the , FALSE version.
So, I went out to Twitter to conduct a highly non-scientific poll to see how many people were using VLOOKUP formulas that end in FALSE.
In the first place, our first answer, Chris Murphy, 100%, and all the other answers 100%.
Never had to use TRUE so far.
99.9999%.
May have used TRUE once.
99.9999%.
100%.
100%.
Here’s someone who doesn't use FALSE but they use 0.
Same thing.
99.9999% Dan just easier to write.
Finally, Luis, 80%.
He uses approximate to create reference tables instead of nested if statements.
Absolutely. Szilvia, 99%. Debra, 99%. 99%.
I mean, really, for me, I'm 99% too and the other 1% is what I'm doing a seminar on that says what's the TRUE version for anyway?
So, let's test this once and for all.
How am I going to test it?
We're going to go out to this article at the Office development center, Office 2010 performance.
It is the article about Excel speeds.
It’s written by Charles Williams.
Charles is a MVP from England, Decision Models Limited.
If you ever have a slow spreadsheet, Charles can make your spreadsheet faster – also Allison and Chad from the Microsoft team, but Charles is just a great resource that you should consider hiring.
I took some code from Charles and we're going to run a macro here that is going to do, let's see how many it's going to do, 14,303 VLOOKUPs into a table that is 116,966 rows tall.
Initially, the table will not be sorted, alright?
So, we'll do ALT+F8 here, and CALCTIME, click RUN, and on the lower right-hand corner, we're watching it count up, and I'm not going to force you to watch this.
I'm going to snip the video but we'll see how long it takes when it's done.
Alright.
There we go.
82.65137 seconds, unsorted.
Click OK.
Alright.
Let's get rid of these.
This is just a regular old VLOOKUP.
Get rid of those.
We will sort this data so that way it is sorted ascending, like the daily tweet suggests, and we'll run the code again.
ALT+F8.
CALCTIME.
RUN.
Alright.
Snip the video.
We'll be right back.
Look at that.
113 seconds.
So, actually, sorting the data slows it down for this large of a data set.
Now, to understand why, let's turn back to Charles Williams.
This is an article he wrote for VLOOKUP Week a few years ago, and if we're using the , FALSE, which seems to be used 99.999% of the time, then Excel is going to do a linear exact match.
It's not going to go halfway and see whether it's before that or not.
It's going to going to read every possible item.
So, the only conceivable way that sorting would help is if we would sort by popularity, bring your most useful item numbers up to the top, and we'll see how that goes.
Wow.
9.46256 seconds.
Sorting by popularity, not AtoZ, is the way to make VLOOKUP faster, but if you really want to make this process faster, we're going to use another trick from the same article by Charles Williams where he suggests doing 2 VLOOKUPs – a VLOOKUP with , TRUE in the first column to see if it's = to the PARTNUMBER, in other words, is it there, and if it is there, then go ahead and do a VLOOKUP with , TRUE, otherwise MISSING.
Let’s take a look at how fast this is.
I have a second macro to put that formula in.
We'll click RUN. Bam. 0.19079 seconds.
An amazing improvement.
Alright.
So, let's recap.
The regular old unsorted , FALSE VLOOKUP that we're all doing, 82.6 seconds.
If you follow the tweet that's out there every single day, it actually slows you down 37%.
If you wanted to make it faster by sorting, you'd have to sort it by popular, 89% savings, but the absolute best way to go, 2 , TRUE VLOOKUPs like the Charles Williams article for a 99.77% speed increase.
If you want a VLOOKUP speed tip, that's the one, not this one that we see on Twitter every day.
Well, hey if you have something interesting about Excel, go ahead and tweet me at MrExcel.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1790: VLOOKUP Does Sorting Help?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen. First, a shout-out to Debra Dalgleish.
Debra is a Excel MVP who monitors Twitter looking for people that she can help in Excel, and, in the course of that, she comes up with funny tweets about Excel and she posts those every Friday.
Just watch for the hashtag Fridayfun Excel tweets and they are hilarious.
I look forward to this every Friday and, sometimes, when I'm bored on my coffee break, I'll go out to Twitter and watch for interesting things to come on, just watching for Excel but, you know, there's a few things.
Willie Mays.
I don't know what Willie Mays knows about Excel.
I'm sure he can't do a VLOOKUP but this tweet gets retweeted all the time, and then the religious group, there's this about average and excel, that gets retweeted over and over and over, and as I'm cruising through looking for good Excel tweets, these come up, and then this one is posted every single day.
VLOOKUP make sure the first column is A-Z and you name the data you’re analyzing, and I see that one every day, and I'm like, what are they talking about, because, as far as I know, VLOOKUP, it's only important to have the first column sorted if you're using the , TRUE version, not the , FALSE version.
So, I went out to Twitter to conduct a highly non-scientific poll to see how many people were using VLOOKUP formulas that end in FALSE.
In the first place, our first answer, Chris Murphy, 100%, and all the other answers 100%.
Never had to use TRUE so far.
99.9999%.
May have used TRUE once.
99.9999%.
100%.
100%.
Here’s someone who doesn't use FALSE but they use 0.
Same thing.
99.9999% Dan just easier to write.
Finally, Luis, 80%.
He uses approximate to create reference tables instead of nested if statements.
Absolutely. Szilvia, 99%. Debra, 99%. 99%.
I mean, really, for me, I'm 99% too and the other 1% is what I'm doing a seminar on that says what's the TRUE version for anyway?
So, let's test this once and for all.
How am I going to test it?
We're going to go out to this article at the Office development center, Office 2010 performance.
It is the article about Excel speeds.
It’s written by Charles Williams.
Charles is a MVP from England, Decision Models Limited.
If you ever have a slow spreadsheet, Charles can make your spreadsheet faster – also Allison and Chad from the Microsoft team, but Charles is just a great resource that you should consider hiring.
I took some code from Charles and we're going to run a macro here that is going to do, let's see how many it's going to do, 14,303 VLOOKUPs into a table that is 116,966 rows tall.
Initially, the table will not be sorted, alright?
So, we'll do ALT+F8 here, and CALCTIME, click RUN, and on the lower right-hand corner, we're watching it count up, and I'm not going to force you to watch this.
I'm going to snip the video but we'll see how long it takes when it's done.
Alright.
There we go.
82.65137 seconds, unsorted.
Click OK.
Alright.
Let's get rid of these.
This is just a regular old VLOOKUP.
Get rid of those.
We will sort this data so that way it is sorted ascending, like the daily tweet suggests, and we'll run the code again.
ALT+F8.
CALCTIME.
RUN.
Alright.
Snip the video.
We'll be right back.
Look at that.
113 seconds.
So, actually, sorting the data slows it down for this large of a data set.
Now, to understand why, let's turn back to Charles Williams.
This is an article he wrote for VLOOKUP Week a few years ago, and if we're using the , FALSE, which seems to be used 99.999% of the time, then Excel is going to do a linear exact match.
It's not going to go halfway and see whether it's before that or not.
It's going to going to read every possible item.
So, the only conceivable way that sorting would help is if we would sort by popularity, bring your most useful item numbers up to the top, and we'll see how that goes.
Wow.
9.46256 seconds.
Sorting by popularity, not AtoZ, is the way to make VLOOKUP faster, but if you really want to make this process faster, we're going to use another trick from the same article by Charles Williams where he suggests doing 2 VLOOKUPs – a VLOOKUP with , TRUE in the first column to see if it's = to the PARTNUMBER, in other words, is it there, and if it is there, then go ahead and do a VLOOKUP with , TRUE, otherwise MISSING.
Let’s take a look at how fast this is.
I have a second macro to put that formula in.
We'll click RUN. Bam. 0.19079 seconds.
An amazing improvement.
Alright.
So, let's recap.
The regular old unsorted , FALSE VLOOKUP that we're all doing, 82.6 seconds.
If you follow the tweet that's out there every single day, it actually slows you down 37%.
If you wanted to make it faster by sorting, you'd have to sort it by popular, 89% savings, but the absolute best way to go, 2 , TRUE VLOOKUPs like the Charles Williams article for a 99.77% speed increase.
If you want a VLOOKUP speed tip, that's the one, not this one that we see on Twitter every day.
Well, hey if you have something interesting about Excel, go ahead and tweet me at MrExcel.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
