Martin from Sweden sends in a question about doing a range VLOOKUP where the lookup table is not sorted. Mike and Bill duel it out in today's Episode 1144.
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:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey, Welcombe back. It's a Dueling Excel podcast, I'm Bill Jelen, we'll have Mike Girvin from ExcelIsFun.
Today's question comes in from Martin, Martin's from Sweden, and Martin watched podcast 1135, where I talked about how to do a VLOOKUP with ,TRUE at the end.
And Martin sends me an evil, evil question, he says “Hey, my lookup table is not sorted and cannot be sorted.” I'm like “Oh that's going to be really, really tough, because the whole thing with a ,TRUE lookup is, it has to be sorted, and if it’s not sorted, we’re kind of in trouble.” So I started to think about this, I said “Hmmm, I wonder if I could isolate which value is the matching value?” and I said “Let's take that 2714 minus all of these values over here!” Alright now, of course, that's going to be an array formula, it's going to return many values, so I need to get the MIN of that whole big thing over there, and we'll do Ctrl+Shift+Enter, and- Oh, oh, oh!
Really what I want is the smallest number that's greater than 0, alright?
So I took that same concept that we just did, I said “Hey, we're going to take H3 minus that whole range.
If that's less than 0, I want a huge number, no-no-no-no.
But otherwise, then I want H3 minus that range, and took the MIN of that whole big thing.” That's the solution right there, that is the piece that solves this whole puzzle.
Alright, so it says “Hey, from 2714, you're 714 higher than the level.” Let's try something else here, let's try 2555 here, 555 higher than the level, alright.
So then, what do I do with that over here?
I take 2555-555 to get back to the bonus level, see what I did?
I found the exact number that I'm looking for, which then I'm using a ,FALSE version of VLOOKUP, or a ,0 version of the MATCH, and then it doesn't have to be sorted at all.
Alright, so now we just have a simple MATCH here, says “Hey, go find 2000 in this range.” and it says that that is in row 2.
And then to get the bonus amount, very simple, just use INDEX of the bonus amounts of that whole value.
Now you know me, I always build things in these big long multi-step formulas.
I said “Hmmm, I wonder if I can get it shorter?” So I did everything up to getting rid of the array formula here, alright, so we have this great big bonus formula all put together.
And then I said “What the heck, let's try it.” And I put the whole thing in one great big formula, Ctrl+Shift+Enter, and sure enough, it works.
Now no one would ever be able to look at this and figure out what the heck is going on, but it works.
So Martin, there's one solution.
Now I'm going to send this over a Mike, we'll see what Mike comes up with!
Mike: Thanks MrExcel!
Wow, I love this formula right here, this Delta, when I first saw it, I was like “What?
Huh?” This is awesome, he goes from the approximate value, and with his formula here, finds the difference between the approximate and the exact value, and then gets the exact value, and then uses that in a lookup!
Totally awesome!
I'm going to take a totally different approach, instead of going from the approximate value and finding the exact value and using that as a lookup.
I'm going to take the unsorted table and sort it in the formula, and then use a straight VLOOKUP or INDEX, or something like that.
OK, I'm going to do this two ways, the first way is what if you have the unsorted table, and you can't sort it, but you could have a separate table over here?
You could easily build a formula that would sort pick the unsorted data and sort it, so I'm going to use the SMALL function.
I'm going to say “Hey, give me that whole array right there.” And I'm going to lock it F4, F4, going down, but not to the side, because when we copy over to Amount, we need that to move over to Amount.
Comma, and then we need, as we copy down, the first smallest, second smallest, third smallest.
I'm going to use ROWS!
And I'm sitting in cell I3, so I'm going to do I$3:I3, that'll increment a number as we go down, close parentheses, Ctrl+Enter, double-click and send it down.
Oops, that won't work, I drag, and then drag it over, and sure enough you can see how that worked perfectly, so I sorted the table, then it's just a simple matter of VLOOKUP.
I'M going to look up this, comma, within this range right here, lock it, F4, ,2 , because I'm finding the amount from the 2nd column.
TRUE, approximate match, you can just leave it off, close parentheses, Ctrl+Enter, and then double-click and send it down.
Now, that's fine if you can put this table over here, but if you can't, we can use a similar SMALL function in memory, to sort the whole column in memory.
So let me right-click, Unhide, and we're going to do, let's just do the SMALL, and see if we can do get the assorted table inside of our formula.
So SMALL, I'm going to take this right here, I'm going to hit the F4 key, comma and the K! the SMALL function can handle array syntax, so you can put {1,2,3,4,5,6,7,8}.
Commas are columns, semicolons are rows, it doesn't matter which one, as long as you have them contained in curly brackets, close parenthesis on the SMALL, and watch this.
When I highlight this and evaluate it, F9, BOOM, there is this unsorted table sorted in formula memory!
I'm going to Ctrl+Z, I'm going to copy that, Ctrl+C.
Now, the problem is, I don't know how to sort the whole table, but I can do one column, and then the other column.
Well, unfortunately, we can't use INDEX or VLOOKUP, but we can use LOOKUP!
So LOOKUP, the screen tip is very help- says “Give me a lookup value, and then the lookup vector, and the result vector!” So I'm going to click on this value right here for a lookup value, comma, there, that SMALL right there is my lookup vector, sorted from unsorted column.
Then I'm going to come to the end here, type a comma, now I need my result vector.
I'm going to Ctrl+V, and all I have to do is change the A to a B, and the A to a B here.
Now it's looking there, it'll sort that as the result vector, close parenthesis.
Now, this is not an array formula, the SMALL can handle this array syntax without Ctrl+Shift+Enter and look up like INDEX and SUMPRODUCT.
It’s a function that can handle arrays without Ctrl+Shift+Enter, so I can just Enter and then copy it down, and sure enough, there it works.
If we come down here, we can see, BOOM, it's got those two tables, it's looking that up, and it returns that a 33.
Now, if you didn't want a hard code this 1-2-3-4-5-6-7-8, like the person, maybe was going to insert rows or something like that.
You could do this ridiculous formula here with ROW, INDIRECT, COUNT, and you can download this workbook from the ExcelIsFun site if you want to look at that one.
But BOOM, there it is, LOOKUP with sorted tables using SMALL.
Alright, we'll see you next trick!
Bill: Mike, that's brilliant!
That's good!
He says “Hey, we're not allowed to sort the table.” You sorted the table right in your formula, brilliant!
Great use of SMALL, excellent, very good.
Well hey, I want to thank everyone for stopping by, Mike and I will see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey, Welcombe back. It's a Dueling Excel podcast, I'm Bill Jelen, we'll have Mike Girvin from ExcelIsFun.
Today's question comes in from Martin, Martin's from Sweden, and Martin watched podcast 1135, where I talked about how to do a VLOOKUP with ,TRUE at the end.
And Martin sends me an evil, evil question, he says “Hey, my lookup table is not sorted and cannot be sorted.” I'm like “Oh that's going to be really, really tough, because the whole thing with a ,TRUE lookup is, it has to be sorted, and if it’s not sorted, we’re kind of in trouble.” So I started to think about this, I said “Hmmm, I wonder if I could isolate which value is the matching value?” and I said “Let's take that 2714 minus all of these values over here!” Alright now, of course, that's going to be an array formula, it's going to return many values, so I need to get the MIN of that whole big thing over there, and we'll do Ctrl+Shift+Enter, and- Oh, oh, oh!
Really what I want is the smallest number that's greater than 0, alright?
So I took that same concept that we just did, I said “Hey, we're going to take H3 minus that whole range.
If that's less than 0, I want a huge number, no-no-no-no.
But otherwise, then I want H3 minus that range, and took the MIN of that whole big thing.” That's the solution right there, that is the piece that solves this whole puzzle.
Alright, so it says “Hey, from 2714, you're 714 higher than the level.” Let's try something else here, let's try 2555 here, 555 higher than the level, alright.
So then, what do I do with that over here?
I take 2555-555 to get back to the bonus level, see what I did?
I found the exact number that I'm looking for, which then I'm using a ,FALSE version of VLOOKUP, or a ,0 version of the MATCH, and then it doesn't have to be sorted at all.
Alright, so now we just have a simple MATCH here, says “Hey, go find 2000 in this range.” and it says that that is in row 2.
And then to get the bonus amount, very simple, just use INDEX of the bonus amounts of that whole value.
Now you know me, I always build things in these big long multi-step formulas.
I said “Hmmm, I wonder if I can get it shorter?” So I did everything up to getting rid of the array formula here, alright, so we have this great big bonus formula all put together.
And then I said “What the heck, let's try it.” And I put the whole thing in one great big formula, Ctrl+Shift+Enter, and sure enough, it works.
Now no one would ever be able to look at this and figure out what the heck is going on, but it works.
So Martin, there's one solution.
Now I'm going to send this over a Mike, we'll see what Mike comes up with!
Mike: Thanks MrExcel!
Wow, I love this formula right here, this Delta, when I first saw it, I was like “What?
Huh?” This is awesome, he goes from the approximate value, and with his formula here, finds the difference between the approximate and the exact value, and then gets the exact value, and then uses that in a lookup!
Totally awesome!
I'm going to take a totally different approach, instead of going from the approximate value and finding the exact value and using that as a lookup.
I'm going to take the unsorted table and sort it in the formula, and then use a straight VLOOKUP or INDEX, or something like that.
OK, I'm going to do this two ways, the first way is what if you have the unsorted table, and you can't sort it, but you could have a separate table over here?
You could easily build a formula that would sort pick the unsorted data and sort it, so I'm going to use the SMALL function.
I'm going to say “Hey, give me that whole array right there.” And I'm going to lock it F4, F4, going down, but not to the side, because when we copy over to Amount, we need that to move over to Amount.
Comma, and then we need, as we copy down, the first smallest, second smallest, third smallest.
I'm going to use ROWS!
And I'm sitting in cell I3, so I'm going to do I$3:I3, that'll increment a number as we go down, close parentheses, Ctrl+Enter, double-click and send it down.
Oops, that won't work, I drag, and then drag it over, and sure enough you can see how that worked perfectly, so I sorted the table, then it's just a simple matter of VLOOKUP.
I'M going to look up this, comma, within this range right here, lock it, F4, ,2 , because I'm finding the amount from the 2nd column.
TRUE, approximate match, you can just leave it off, close parentheses, Ctrl+Enter, and then double-click and send it down.
Now, that's fine if you can put this table over here, but if you can't, we can use a similar SMALL function in memory, to sort the whole column in memory.
So let me right-click, Unhide, and we're going to do, let's just do the SMALL, and see if we can do get the assorted table inside of our formula.
So SMALL, I'm going to take this right here, I'm going to hit the F4 key, comma and the K! the SMALL function can handle array syntax, so you can put {1,2,3,4,5,6,7,8}.
Commas are columns, semicolons are rows, it doesn't matter which one, as long as you have them contained in curly brackets, close parenthesis on the SMALL, and watch this.
When I highlight this and evaluate it, F9, BOOM, there is this unsorted table sorted in formula memory!
I'm going to Ctrl+Z, I'm going to copy that, Ctrl+C.
Now, the problem is, I don't know how to sort the whole table, but I can do one column, and then the other column.
Well, unfortunately, we can't use INDEX or VLOOKUP, but we can use LOOKUP!
So LOOKUP, the screen tip is very help- says “Give me a lookup value, and then the lookup vector, and the result vector!” So I'm going to click on this value right here for a lookup value, comma, there, that SMALL right there is my lookup vector, sorted from unsorted column.
Then I'm going to come to the end here, type a comma, now I need my result vector.
I'm going to Ctrl+V, and all I have to do is change the A to a B, and the A to a B here.
Now it's looking there, it'll sort that as the result vector, close parenthesis.
Now, this is not an array formula, the SMALL can handle this array syntax without Ctrl+Shift+Enter and look up like INDEX and SUMPRODUCT.
It’s a function that can handle arrays without Ctrl+Shift+Enter, so I can just Enter and then copy it down, and sure enough, there it works.
If we come down here, we can see, BOOM, it's got those two tables, it's looking that up, and it returns that a 33.
Now, if you didn't want a hard code this 1-2-3-4-5-6-7-8, like the person, maybe was going to insert rows or something like that.
You could do this ridiculous formula here with ROW, INDIRECT, COUNT, and you can download this workbook from the ExcelIsFun site if you want to look at that one.
But BOOM, there it is, LOOKUP with sorted tables using SMALL.
Alright, we'll see you next trick!
Bill: Mike, that's brilliant!
That's good!
He says “Hey, we're not allowed to sort the table.” You sorted the table right in your formula, brilliant!
Great use of SMALL, excellent, very good.
Well hey, I want to thank everyone for stopping by, Mike and I will see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
