In today's dueling podcast, we need to look up a value in one of three different tables depending on the product selected. Mike and Bill show many ways to solve the problem in Episode 1165.
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 welcome back, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from ExcelIsFun.
Well yeah, it's Thursday, it's a day early, but tomorrow's Christmas Eve, this is supposed to be our last podcast before the holiday.
Mike sent me this question that he received, what a great question, we need to do a VLOOKUP, but we need to do a ,TRUE version of the VLOOKUP, but that's not the real problem.
Here's the trick, depending on what product they sell, there's one of three commission tables that have to be used, and those commission tables all have different rates.
So here's commission table 1, you see there's levels at 1000, 2000, 5000.
Here's commission table 2, oh look at that, different levels, 200, 700, 1500, and then commission table 3.
The one thing that I really like about all of these is, they all happen to be in A1:B7, that makes life a little bit simpler.
Alright, so we're going to start out with =VLOOKUP, just like you would expect, and we're going to go look up this sales amount, and now we need to go look up the right table.
Alright well, I'm going to do that using INDIRECT, the INDIRECT function says “Hey, I'm going to provide you some text, and that text is going to evaluate to something that looks like a cell reference.” Alright, so here's the trick, CHOOSE table 1, 2, or 3, and now I have to provide it the name of that sheet.
So if it's table 1, it's M12, if it's table 2 it's M55, and if it's table 3, it's M43.
Now depending on which sheet we get, we're always going to have to append every single time after that the exclamation point, and then A1:B7.
Alright, now notice this, I'm not putting $ in there, I'm going to copy this formula down.
The one problem, most of the time it's a problem, with INDIRECT, is that as I copy that down, the reference doesn't change like it normally would.
But in this case it's actually to our advantage, as I cut this down, the reference isn't going to change, cool, right?
OK, so that INDIRECT is going to evaluate to one of those three sheets, and point to A1:B7.
Then, we need the second column, and then not a ,FALSE like we always do, ,TRUE , but it turns out that if you don't put anything, it's the same as putting ,TRUE ! So we'll just finish that there, and we get 1%.
Let's format that maybe as a percentage, and double-click to shoot it down.
Alright, and we'll do a little test here, so here, in commission table 3, 920$, will go take a look at the 3rd commission table, should be 1%, let's come back, and hey, it worked, isn't that cool?
Alright, Mike says that he has a great solution for this.
So Mike, let's see what you have!
Mike: Thanks MrExcel!
Whoa!
I love this formula.
Two things about it!
One is, just from the sales number and the commission table number three, he built sheet references.
So one, he didn't have to mess around with doing sheet references and two, I love this trick with INDIRECT.
Of course, because it's text being copied down, it's as if it is absolute.
Alright, I'm going to do something similar but, I'm going to go ahead and not use INDIRECT.
INDIRECT is volatile, and I think it is the way to go in this situation, but some people don't like volatile functions, volatile just means every time you do something it updates and recalculates.
Alright, I'm going to do a VLOOKUP just like MrExcel, and then for the table I'm going to use just the CHOOSE.
Now the index number, I'm going to go here, and the way CHOOSE works is if you put a 1, 2, or 3 here, or any number, you just list all of the things that you want to choose from our look up.
So this will be either 1, 2, or 3, depending on what's there.
Value 1, guess what, CHOOSE can have numbers typed in, text, functions, formulas, or references!
So I'm just going to click here and hit the F4 key, and then comma, so you could see it evolving up here.
And then click over here, highlight that range and F4 to lock it, and then comma, and I can see the formula evolving up here.
And then come back over here and get that range right there, F4, right, so you could see this evolving up here, close parentheses.
And then the screen tips’ helping me out greatly here, it says “Hey, you just put the table in.” So to get to the column index, I'm going to type a comma, column index, it's always going to be 2.
And then we don't need the last one, because this is approximate match look up, so close parentheses there, and Ctrl+Enter.
Now I can double click and send it down, and sure enough, now CHOOSE is choosing amongst three sheet references that I had to put in manually based on whatever's in 3 cells to my left.
And of course, the VLOOKUP is looking up the right number.
Alright, throw it back over to MrExcel!
Bill: Mike, I have to try and Evaluate Formula, I can't believe that this works, that CHOOSE returns some text, in that text looks like a cell reference.
Why don't you need INDIRECT to run that?
Can hardly believe it.
Also I love Isaac checking in there as you were doing the trick.
So hey, I can improve mine a little bit.
I came here to these tables, and see I created some range names, so we have Machine12, Machine55 for this range, and then Machine43 for that range.
So then I come back to my formula, and thinking about it, I don't even have to build anything, I can just say INDIRECT(C5), so in this case C5 is Machine43.
So we're saying “Hey, go take the named range, we don't have to build which sheet it's on or anything like that.” You can see that that works, alright, check it out!
Mike: Wow!
That's it, point to MrExcel!
INDIRECT to name, and then just use this column over here.
INDIRECT, look at that, so beautiful, so efficient!
Now, I'm going to verify just quickly Machine12.
Yes, sure enough, look at that, Machine4- look at that, I'm using the name box to jump, to navigate.
So I'm going to use those same three, I'm going to copy the sheet over, I'm going to point to this and hold Ctrl and then click and drag, and I'm going to let go of the mouse, not Ctrl, and then BOOM!
I can get the second sheet here, I'll use the same names, will do =VLOOKUP, lookup value of B1 cell to my left, comma, and then I'll just use CHOOSE again, just like I did the first time.
Column index number, but here I’ll comma, and then I can put named range, the first time I put the actual sheet reference.
But now the names exist, I'll use F3 for Paste Name, and one is 12, comma, and then F3 to paste the name 55 is the second one, comma, and then F3 key to paste the name, and finally I'll do 43.
So named ranges can also be used in CHOOSE, I'll ),2), Ctrl+Enter.
And so, not quite as elegant as if you look up and INDIRECT, but using names you can use CHOOSE, not volatile.
Alright, I'll throw it back over to MrExcel!
Bill: Mike, you know, people tell me they love the Dueling Excel podcast, and I can see why! right, you think, you're tuning in for something called look up three tables.
And along the way you learn that you can Ctrl-drag to copy a sheet, you'll learn that you can use F3 to paste names, and I learn that you can use range names and the CHOOSE.
And I got to tell you, I never knew this.
So, I'm going to click inside the CHOOSE here and hit the Fx button right next to the Formula bar, that brings up the function argument, this is like Help on every single argument.
So when I click in here, “value1, value2… are one of 254 numbers, or cell references, or defined names, formulas, functions, or text arguments from which CHOOSE selects.” Look at that, you can use defined names.
Now I'm kind of wondering, if Mike sits around at lunch hour just going through all 355 functions reading the Excel arguments.
That's obscure, I love it!
Alright hey, last podcast before the holiday, hope everyone has a great safe holiday weekend, catch you next week 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 welcome back, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from ExcelIsFun.
Well yeah, it's Thursday, it's a day early, but tomorrow's Christmas Eve, this is supposed to be our last podcast before the holiday.
Mike sent me this question that he received, what a great question, we need to do a VLOOKUP, but we need to do a ,TRUE version of the VLOOKUP, but that's not the real problem.
Here's the trick, depending on what product they sell, there's one of three commission tables that have to be used, and those commission tables all have different rates.
So here's commission table 1, you see there's levels at 1000, 2000, 5000.
Here's commission table 2, oh look at that, different levels, 200, 700, 1500, and then commission table 3.
The one thing that I really like about all of these is, they all happen to be in A1:B7, that makes life a little bit simpler.
Alright, so we're going to start out with =VLOOKUP, just like you would expect, and we're going to go look up this sales amount, and now we need to go look up the right table.
Alright well, I'm going to do that using INDIRECT, the INDIRECT function says “Hey, I'm going to provide you some text, and that text is going to evaluate to something that looks like a cell reference.” Alright, so here's the trick, CHOOSE table 1, 2, or 3, and now I have to provide it the name of that sheet.
So if it's table 1, it's M12, if it's table 2 it's M55, and if it's table 3, it's M43.
Now depending on which sheet we get, we're always going to have to append every single time after that the exclamation point, and then A1:B7.
Alright, now notice this, I'm not putting $ in there, I'm going to copy this formula down.
The one problem, most of the time it's a problem, with INDIRECT, is that as I copy that down, the reference doesn't change like it normally would.
But in this case it's actually to our advantage, as I cut this down, the reference isn't going to change, cool, right?
OK, so that INDIRECT is going to evaluate to one of those three sheets, and point to A1:B7.
Then, we need the second column, and then not a ,FALSE like we always do, ,TRUE , but it turns out that if you don't put anything, it's the same as putting ,TRUE ! So we'll just finish that there, and we get 1%.
Let's format that maybe as a percentage, and double-click to shoot it down.
Alright, and we'll do a little test here, so here, in commission table 3, 920$, will go take a look at the 3rd commission table, should be 1%, let's come back, and hey, it worked, isn't that cool?
Alright, Mike says that he has a great solution for this.
So Mike, let's see what you have!
Mike: Thanks MrExcel!
Whoa!
I love this formula.
Two things about it!
One is, just from the sales number and the commission table number three, he built sheet references.
So one, he didn't have to mess around with doing sheet references and two, I love this trick with INDIRECT.
Of course, because it's text being copied down, it's as if it is absolute.
Alright, I'm going to do something similar but, I'm going to go ahead and not use INDIRECT.
INDIRECT is volatile, and I think it is the way to go in this situation, but some people don't like volatile functions, volatile just means every time you do something it updates and recalculates.
Alright, I'm going to do a VLOOKUP just like MrExcel, and then for the table I'm going to use just the CHOOSE.
Now the index number, I'm going to go here, and the way CHOOSE works is if you put a 1, 2, or 3 here, or any number, you just list all of the things that you want to choose from our look up.
So this will be either 1, 2, or 3, depending on what's there.
Value 1, guess what, CHOOSE can have numbers typed in, text, functions, formulas, or references!
So I'm just going to click here and hit the F4 key, and then comma, so you could see it evolving up here.
And then click over here, highlight that range and F4 to lock it, and then comma, and I can see the formula evolving up here.
And then come back over here and get that range right there, F4, right, so you could see this evolving up here, close parentheses.
And then the screen tips’ helping me out greatly here, it says “Hey, you just put the table in.” So to get to the column index, I'm going to type a comma, column index, it's always going to be 2.
And then we don't need the last one, because this is approximate match look up, so close parentheses there, and Ctrl+Enter.
Now I can double click and send it down, and sure enough, now CHOOSE is choosing amongst three sheet references that I had to put in manually based on whatever's in 3 cells to my left.
And of course, the VLOOKUP is looking up the right number.
Alright, throw it back over to MrExcel!
Bill: Mike, I have to try and Evaluate Formula, I can't believe that this works, that CHOOSE returns some text, in that text looks like a cell reference.
Why don't you need INDIRECT to run that?
Can hardly believe it.
Also I love Isaac checking in there as you were doing the trick.
So hey, I can improve mine a little bit.
I came here to these tables, and see I created some range names, so we have Machine12, Machine55 for this range, and then Machine43 for that range.
So then I come back to my formula, and thinking about it, I don't even have to build anything, I can just say INDIRECT(C5), so in this case C5 is Machine43.
So we're saying “Hey, go take the named range, we don't have to build which sheet it's on or anything like that.” You can see that that works, alright, check it out!
Mike: Wow!
That's it, point to MrExcel!
INDIRECT to name, and then just use this column over here.
INDIRECT, look at that, so beautiful, so efficient!
Now, I'm going to verify just quickly Machine12.
Yes, sure enough, look at that, Machine4- look at that, I'm using the name box to jump, to navigate.
So I'm going to use those same three, I'm going to copy the sheet over, I'm going to point to this and hold Ctrl and then click and drag, and I'm going to let go of the mouse, not Ctrl, and then BOOM!
I can get the second sheet here, I'll use the same names, will do =VLOOKUP, lookup value of B1 cell to my left, comma, and then I'll just use CHOOSE again, just like I did the first time.
Column index number, but here I’ll comma, and then I can put named range, the first time I put the actual sheet reference.
But now the names exist, I'll use F3 for Paste Name, and one is 12, comma, and then F3 to paste the name 55 is the second one, comma, and then F3 key to paste the name, and finally I'll do 43.
So named ranges can also be used in CHOOSE, I'll ),2), Ctrl+Enter.
And so, not quite as elegant as if you look up and INDIRECT, but using names you can use CHOOSE, not volatile.
Alright, I'll throw it back over to MrExcel!
Bill: Mike, you know, people tell me they love the Dueling Excel podcast, and I can see why! right, you think, you're tuning in for something called look up three tables.
And along the way you learn that you can Ctrl-drag to copy a sheet, you'll learn that you can use F3 to paste names, and I learn that you can use range names and the CHOOSE.
And I got to tell you, I never knew this.
So, I'm going to click inside the CHOOSE here and hit the Fx button right next to the Formula bar, that brings up the function argument, this is like Help on every single argument.
So when I click in here, “value1, value2… are one of 254 numbers, or cell references, or defined names, formulas, functions, or text arguments from which CHOOSE selects.” Look at that, you can use defined names.
Now I'm kind of wondering, if Mike sits around at lunch hour just going through all 355 functions reading the Excel arguments.
That's obscure, I love it!
Alright hey, last podcast before the holiday, hope everyone has a great safe holiday weekend, catch you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
