How to Do Unsorted Range VLOOKUP on Unsorted Lookup Table

Bill Mr. Excel Jelen vs. Mike Gel Girvin Excel is Fun Bill: Hey, I’m Bill Jelen from Mr. Excel.com and I’ve got a cool Excel tip for you today. Mike: Hey this is Mike Gel Girvin at Excel is Fun at YouTube and I have a different way to do that. Bill: Hey welcome back it’s a dueling Excel Podcast, I’m Bill Jelen. We’ll have Mike Girvin from Excel is Fun. Today this question comes in from Martin. Martin is from Sweden and Martin watched podcast 11:35 where I talked about how to do a VLOOKUP with comma 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 comma true look up is it has to be sorted and if it is not sorted we’re kind of in trouble. So I started to think about I said hmm hmm hmm hmm. I wonder if I could isolate which value is the matching value and said I said let’s take that 27/14 minus all of these values over here, all of these values. Alright now of course that’s going to be the rate formula that’s going to return in many values so I need to get them in the min of that whole big thing over there and I will do Control Shift Enter and really what I want is the smallest number that’s greater than zero. Greater zero, alright so I too that same concept that we just did and said, “Hey we’re going to take H3 minus that whole range if that’s less than zero I want a huge number.” But otherwise then I want H3 minus that range and took the min of that whole big thing, that’s the solution weather that is the piece that solves this whole puzzle. Alright, so it says hey from 27/14 your 714 higher than the level. Let’s try something else here. Let’s try 2555 you’re 5555 higher than the level alright so then what do I do with that over here I take 2555 minus 5555 to get back to the bonus level. See what I did. I found he exact number that I’m looking for which then I’m using a comma false version of e-lookup or a comma zero version of the match and then it doesn’t have to be sorted at all. Alright, now we just have a simple match here that says hey go find 2000 in this range and says that, that is in route two and then to get the bonus amount, very simple just use index of the bonus amounts that hold value. Now you know me, I always built things in these big long multi-step formulas I said “I wonder if I can get it shorter.” So I did everything up to getting rid of the rate formula here alright so we have this great big bonus formula all put together and I said what the heck let’s try it. And I put the whole thing in one great big formula control shift enter and sure enough it works a million well whatever but 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, will see what Mike comes up with. Mike: Thanks Mr. Excel. 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 this formula here finds the difference between the approximate and the exact thing and it gets the exact value and then uses that in a look up. 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 index or something like that. Okay, I’m going to do this two ways, the first way is what if you have the sun sort of table and you can’t sort it but you can have separate table over here. You can easily build a formula that would sort take this 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.” I don’t want to lock it, F4, F4 going down but not to the side because when they copy over till now we need that to move over till now, and then we need a we copy down the first small, the second smalls, thirds smalls, so I’m going to use rose and I’m sitting in cell I3 so I’m going to do I$3:I3. That will increment a number as we go down. Close parenthesis Control Enter. Double click and send it down oops that won’t work and drag. And then drag it over and sure enough you can see how that work perfectly. So I sorted the table and it is just a simple matter of VLOOKUP. I’m going to look up this, within this range right here lock it, F4, 2 because I’ finding the amount from the second column true approximate mach, you can just leave it off so close parenthesis CONTROL 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, we can use a similar small function in memory to sort the whole column in memory so let me right click on hide 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, and the K the small function can handle an array of syntax so you can put urly bracket one, two, three, four five six seven eight. Commas or columns semicolons are rows it doesn’t matter which one as long as you have found contained in curly brackets. Close parenthesis, oops 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 CONTROL Z. I’m going to copy that CONTROL 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 and so lookup the screen tip its very place is give me a look up value and then the lookup vector and the result vector. So I’m going to click on this value right here for a look up value, there that small right there is my lookup vector sorted from unsorted column and I’m going to come to the end here. Type a comma and now I need my result vector. I’m going to CONTROL V and all I have to do is change the A to a B and the A to a B here. Now let’s look in there, it will sort that as the result vector close parenthesis. Now this is not an array formula, the small can handle this array syntax without control shift enter and look up like index in some product is a function that can handle arrays without control shift enter so I can just enter and then copy it down and sure enough there it works. If you come down here we can see boom, it’s got those two tables. It’s looking that up and it returns that at 33. Now if you didn’t want a hardcode this 1, 2, 3, 4, 5, 6, 7, 8 like the person maybe was going to insert rows or something like that but you could do this ridiculous formula here with row indirect count and you can download this workbook from the excel as fun site if you want to look at that one but boom there it is. Look up with sorted tables you seen small. Alright we’ll see you next track. 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 I can all see you next for another dueling excel podcast from Mr. Excel and excel is fun.