+Rahim Zulfiqar Ali asks why VLOOKUP(,,,True) returns certain wrong answers when the table is not sorted. The better question would be how it returns the right answer at all when you break the rules. Today, I explain the gory details behind the Binary Search used by the VLOOKUP(,,,True) and show how manipulating key values at the quartiles of the lookup table can send the VLOOKUP into the wrong quadrant of the table. Bottom line: Don't use the VLOOKUP(,,,True) with an unsorted table. Or.... just use VLOOKUP(,,,False) like 99% of the rest of the world.
Transcript of the video:
The MrExcel podcast is sponsored by eduPOW – home of the $5 Courses.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1925.
Explain the VLOOKUP(True) Binary Search Method, also known as: Why is my VLOOKUP giving me the wrong answer when I break the rules?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in in a discussion on Google+: Rahim is saying: Hey, why is the VLOOKUP TRUE giving me the wrong answer?
And I explained, that if you're doing a VLOOKUP(,,,True) with unsorted data, you're not going to get the correct result.
Or you might get the correct result, but if you do, it was just dumb luck.
And Rahim comes back and he says: now, can you explain this to me?
And I'm sorry, I hate to discuss VLOOKUP(True), because no one uses VLOOKUP(True).
I mean, rub up my Twitter Poll here, almost everyone says 99.999% False.
Luis was the only guy, who answered the Twitter Poll, that actually had a reason: “I use Approximate Match to create reference tables instead of nested IF statements”.
And that's a great use for VLOOKUP(True).
But the simple fact is: no one, I've run into in the real world, is using these.
It's always the False version, the only time I'm using True, is when I'm in a class, I have to explain what it is for, or when I'm answering this type of question: why is the True giving me the wrong answer, when I'm breaking the rules?
Here's the pie chart: 99,9% of the world doing an Exact Match, where the VLOOKUP ends and falls.
It does not matter which way your table is sorted.
It could be completely random and you're going to get the answer.
This only applies to the 1% of the people doing the Approximate Match, True version.
And for that tiny minority of the people, that is the one place, where the table must be sorted.
I keep telling, I keep hearing people telling me that the VLOOKUP table has to be sorted and that's not true 99% of the time.
Here's my graphic, so: VLOOKUP(,,,FALSE) Sorting does not matter, right?
This is what everyone is running into.
But, I will have to increase the zoom, when you're doing VLOOKUP(,,,True), if your table is not sorted, you're most likely not going to get the right results.
Okay, so, Rahim pushes back and Rahim says: tell me why?
Why I'm getting the wrong result?
Because you're breaking the rules, Rahim.
You're not allowed to do it.
But to put this one to bed once and for all: let's talk about what a Binary Search is.
So, in every case here: I have 11 values in my lookup table and I am looking up the number 5.
=VLOOKUP(5,F2:G12,2,TRUE) All right, so a Binary Search says: hey, here's our table, we're going to split that table at the midpoint.
So, with this table, that has an odd number of items, the first place, that it's going to look, is item number 6.
It's going to say: is item number 6 larger or smaller than what I'm looking for?
So right here, in this table, the VLOOKUP(,,,True), it's looking for a 5.
It comes cruising in here, first place that looks, is the 11.
And 11 is larger than 5, so then it sends the search routine up into the top half of the table.
Now, on the top half of the table we have five items, so it's going to look at the midpoint of the five items, which is the 5th item, and we get C. All right, this cell, this 6th cell, the midpoint cell is also critical in determining what answer we're going to get.
All right, so here I'm playing with the table, let's come over here, two columns: J and K.
Exact same VLOOKUP formula, looking up number 5.
Exact same table with 11 points.
It comes cruising into the middle of the table, it sees -3, says: hello, hey, that's smaller than what I'm looking for, so therefore I have to go look at the bottom half of the table.
Looks at the bottom half of the table, here's the midpoint, it sees the 7, says: oh, I have to go up from there and then we get the 5, and the H. Alright, so based on whatever value is in that midpoint cell: that controls which half of the table the Binary Search is going to go look into.
All right, now, here's another one.
So we're using -3 again.
That sends it down into the bottom part of the table.
Once we're in the bottom part of the table, there is five values to look through.
it's going to look at the midpoint again and so, here I'm manipulating the results, right?
I put a -2 here and that tells Excel: oh hey, look, in this bottom half of the table, now we're at -2, so I must have to go down from here and I don't know whether look at 3 or 5 first, I'm not sure.
But it finds the 5 and we get the K.
But if I would again manipulate the table, so here's the -3 to send me to the lower half.
Here's the midpoint, here's the 7, which tells Excel that it has to be above this, then it has to choose between the- 2 and the 3 and we get the result from the 3, because that's, you know, that's the… well, people say that the True version is the Approximate Match.
It's returning the value just lower.
But it's not really doing that at all.
It's hopping in here at the middle and then going into the midpoint of that, and then it's trying to find the middle.
So even though 5 is in the table twice, because I've manipulated these midpoints, that it looks at.
I've sent it into a part of the table where it's not going to find the result.
And again, I hate discussing this, you're breaking the rules.
Don't do a VLOOKUP(,,,True) unless the table is sorted.
It's not going to give you the right answers.
All right, now, so here I had 11 rows.
What happens when we have 10 rows and even amount?
So same kind of thing down here, it becomes the 5th cell.
It could be the 5th cell, could be the 6th cell, but it turns out, it's the 5th cell.
That sends it either down to the bottom half of the table, or up into the top half of the table.
So, whether we get the C or the H, depends on that 5th cell.
So, we're looking for the midpoint, it's an odd number, it is the number right in the middle.
If it's an even number, it's the, you know, the number one above the fictional middle that doesn't exist, like cell or row 20-and-a-half.
There is no row 20.5, so it's going to go to the one above that.
So, I hope that explains it.
I feel like this is a wasted podcast, because 99% of world is using the “,False”.
The other 1%, if they follow the rules, this is not an issue at all.
It is just for the people, who are on that weird edge case, that they're using the True and it refused to sort their table and they need to understand what's going on.
It's just, again, it's a crapshoot, whether you're going to get the right answer.
If you do happen to get the right answer, you just happen to luck out.
If your table is not sorted, you're just playing with fire.
Don't, don't, don't do it.
Wow, I sound like such an angry person here, I'm sorry.
I want to thank everyone for stopping by, we'll see you on Monday, for hopefully a more upbeat netcast – MrExcel.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1925.
Explain the VLOOKUP(True) Binary Search Method, also known as: Why is my VLOOKUP giving me the wrong answer when I break the rules?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in in a discussion on Google+: Rahim is saying: Hey, why is the VLOOKUP TRUE giving me the wrong answer?
And I explained, that if you're doing a VLOOKUP(,,,True) with unsorted data, you're not going to get the correct result.
Or you might get the correct result, but if you do, it was just dumb luck.
And Rahim comes back and he says: now, can you explain this to me?
And I'm sorry, I hate to discuss VLOOKUP(True), because no one uses VLOOKUP(True).
I mean, rub up my Twitter Poll here, almost everyone says 99.999% False.
Luis was the only guy, who answered the Twitter Poll, that actually had a reason: “I use Approximate Match to create reference tables instead of nested IF statements”.
And that's a great use for VLOOKUP(True).
But the simple fact is: no one, I've run into in the real world, is using these.
It's always the False version, the only time I'm using True, is when I'm in a class, I have to explain what it is for, or when I'm answering this type of question: why is the True giving me the wrong answer, when I'm breaking the rules?
Here's the pie chart: 99,9% of the world doing an Exact Match, where the VLOOKUP ends and falls.
It does not matter which way your table is sorted.
It could be completely random and you're going to get the answer.
This only applies to the 1% of the people doing the Approximate Match, True version.
And for that tiny minority of the people, that is the one place, where the table must be sorted.
I keep telling, I keep hearing people telling me that the VLOOKUP table has to be sorted and that's not true 99% of the time.
Here's my graphic, so: VLOOKUP(,,,FALSE) Sorting does not matter, right?
This is what everyone is running into.
But, I will have to increase the zoom, when you're doing VLOOKUP(,,,True), if your table is not sorted, you're most likely not going to get the right results.
Okay, so, Rahim pushes back and Rahim says: tell me why?
Why I'm getting the wrong result?
Because you're breaking the rules, Rahim.
You're not allowed to do it.
But to put this one to bed once and for all: let's talk about what a Binary Search is.
So, in every case here: I have 11 values in my lookup table and I am looking up the number 5.
=VLOOKUP(5,F2:G12,2,TRUE) All right, so a Binary Search says: hey, here's our table, we're going to split that table at the midpoint.
So, with this table, that has an odd number of items, the first place, that it's going to look, is item number 6.
It's going to say: is item number 6 larger or smaller than what I'm looking for?
So right here, in this table, the VLOOKUP(,,,True), it's looking for a 5.
It comes cruising in here, first place that looks, is the 11.
And 11 is larger than 5, so then it sends the search routine up into the top half of the table.
Now, on the top half of the table we have five items, so it's going to look at the midpoint of the five items, which is the 5th item, and we get C. All right, this cell, this 6th cell, the midpoint cell is also critical in determining what answer we're going to get.
All right, so here I'm playing with the table, let's come over here, two columns: J and K.
Exact same VLOOKUP formula, looking up number 5.
Exact same table with 11 points.
It comes cruising into the middle of the table, it sees -3, says: hello, hey, that's smaller than what I'm looking for, so therefore I have to go look at the bottom half of the table.
Looks at the bottom half of the table, here's the midpoint, it sees the 7, says: oh, I have to go up from there and then we get the 5, and the H. Alright, so based on whatever value is in that midpoint cell: that controls which half of the table the Binary Search is going to go look into.
All right, now, here's another one.
So we're using -3 again.
That sends it down into the bottom part of the table.
Once we're in the bottom part of the table, there is five values to look through.
it's going to look at the midpoint again and so, here I'm manipulating the results, right?
I put a -2 here and that tells Excel: oh hey, look, in this bottom half of the table, now we're at -2, so I must have to go down from here and I don't know whether look at 3 or 5 first, I'm not sure.
But it finds the 5 and we get the K.
But if I would again manipulate the table, so here's the -3 to send me to the lower half.
Here's the midpoint, here's the 7, which tells Excel that it has to be above this, then it has to choose between the- 2 and the 3 and we get the result from the 3, because that's, you know, that's the… well, people say that the True version is the Approximate Match.
It's returning the value just lower.
But it's not really doing that at all.
It's hopping in here at the middle and then going into the midpoint of that, and then it's trying to find the middle.
So even though 5 is in the table twice, because I've manipulated these midpoints, that it looks at.
I've sent it into a part of the table where it's not going to find the result.
And again, I hate discussing this, you're breaking the rules.
Don't do a VLOOKUP(,,,True) unless the table is sorted.
It's not going to give you the right answers.
All right, now, so here I had 11 rows.
What happens when we have 10 rows and even amount?
So same kind of thing down here, it becomes the 5th cell.
It could be the 5th cell, could be the 6th cell, but it turns out, it's the 5th cell.
That sends it either down to the bottom half of the table, or up into the top half of the table.
So, whether we get the C or the H, depends on that 5th cell.
So, we're looking for the midpoint, it's an odd number, it is the number right in the middle.
If it's an even number, it's the, you know, the number one above the fictional middle that doesn't exist, like cell or row 20-and-a-half.
There is no row 20.5, so it's going to go to the one above that.
So, I hope that explains it.
I feel like this is a wasted podcast, because 99% of world is using the “,False”.
The other 1%, if they follow the rules, this is not an issue at all.
It is just for the people, who are on that weird edge case, that they're using the True and it refused to sort their table and they need to understand what's going on.
It's just, again, it's a crapshoot, whether you're going to get the right answer.
If you do happen to get the right answer, you just happen to luck out.
If your table is not sorted, you're just playing with fire.
Don't, don't, don't do it.
Wow, I sound like such an angry person here, I'm sorry.
I want to thank everyone for stopping by, we'll see you on Monday, for hopefully a more upbeat netcast – MrExcel.
