I keep hearing a common misconception that the lookup table in the VLOOKUP has to be sorted. In many cases, this is not true. Episode 781 looks at the optional fourth parameter of VLOOKUP.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
You know, I've been doing these Excel seminars and, almost every time when I talk about VLOOKUP, I hear someone in the audience say that the lookup table has to be sorted in ascending sequence and there's this huge misconception out there, and let's take a look at what's going on.
If we build a VLOOKUP, so, we want to look up this value in A2 and our table is over here in E and F, and I want the 2nd value, and we just build the VLOOKUP like that without the fourth parameter, then it's true.
In this case, you're going to get wrong answers when the item isn't found.
So, here…and we get answers all the way through but they're not necessarily the right answers.
So, a lot of people say, well, it has to be sorted, and we click AtoZ and sure enough now we seem to get the right items but, in fact, if the item is not found, we're going to get the item just before it alphabetically, which is not the right thing to do at all.
So, if you find yourself saying that you have to make your VLOOKUP table be sorted in order to work, you’re in trouble.
Let’s undo that and go back to the original formula, and we're going to add a secret fourth parameter.
I mean, secret, it came around in Excel.
It was never there in Lotus, but, in Excel, we have to put , FALSE.
, FALSE says, hey, we're not going to accept close matches.
We either find the exact right match or we want an N/A, and so we get N/As here when there aren't matches.
That's important to see the N/A. I don't want the closest SKU.
I want to see that the SKU is missing so I know I had to go get it in my table, and now, because we're using , FALSE, this table does not have to be sorted at all.
So, here's IB2 and we'll search through IB2.
It's giving us ITEM 13.
It's going to give us the right item every time and, when it can't be found, it's going to give us an N/A error.
[ =VLOOKUP(A2,$E$2:$F$26,2,FALSE) ] So, if you've been using the VLOOKUPs without the , FALSE, you're basically defaulting to the range version of VLOOKUP which is going to give you the closest item and, yeah, in that case, it does have to be sorted, but, in reality, 99% of the VLOOKUPs that I do have , FALSE at the end, and that lookup table can be in any sequence.
It works perfectly well.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
You know, I've been doing these Excel seminars and, almost every time when I talk about VLOOKUP, I hear someone in the audience say that the lookup table has to be sorted in ascending sequence and there's this huge misconception out there, and let's take a look at what's going on.
If we build a VLOOKUP, so, we want to look up this value in A2 and our table is over here in E and F, and I want the 2nd value, and we just build the VLOOKUP like that without the fourth parameter, then it's true.
In this case, you're going to get wrong answers when the item isn't found.
So, here…and we get answers all the way through but they're not necessarily the right answers.
So, a lot of people say, well, it has to be sorted, and we click AtoZ and sure enough now we seem to get the right items but, in fact, if the item is not found, we're going to get the item just before it alphabetically, which is not the right thing to do at all.
So, if you find yourself saying that you have to make your VLOOKUP table be sorted in order to work, you’re in trouble.
Let’s undo that and go back to the original formula, and we're going to add a secret fourth parameter.
I mean, secret, it came around in Excel.
It was never there in Lotus, but, in Excel, we have to put , FALSE.
, FALSE says, hey, we're not going to accept close matches.
We either find the exact right match or we want an N/A, and so we get N/As here when there aren't matches.
That's important to see the N/A. I don't want the closest SKU.
I want to see that the SKU is missing so I know I had to go get it in my table, and now, because we're using , FALSE, this table does not have to be sorted at all.
So, here's IB2 and we'll search through IB2.
It's giving us ITEM 13.
It's going to give us the right item every time and, when it can't be found, it's going to give us an N/A error.
[ =VLOOKUP(A2,$E$2:$F$26,2,FALSE) ] So, if you've been using the VLOOKUPs without the , FALSE, you're basically defaulting to the range version of VLOOKUP which is going to give you the closest item and, yeah, in that case, it does have to be sorted, but, in reality, 99% of the VLOOKUPs that I do have , FALSE at the end, and that lookup table can be in any sequence.
It works perfectly well.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
