The Looong awaited return is today! Dueling Excel with Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen return with a LOOKUP Spectacular! Today, in Episode #1631, Mike and Bill work out means to find a variety of results using differing methods of LOOKUP to get the figures. This one is tricky - one of the LOOKUPS uses the default but 'rarely-used' Approximate Match version of VLOOKUP. Follow along as Dueling Excel paves the way to a few tricks and shortcuts you may find useful.
Dueling Excel Podcast #109...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #109...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back, it’s time for another duelling Excel, I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun this is our episode 110: 3 VLOOKUP’s.
Okay today's question from Judy at YouTube, I have listed with five product choices, another list with six packaging choices and a list that shows the discount and for each of these cells we need to look up those tables.
Let's take a look at a table, so here's our product table, here's our packaging table and then our discount table.
Now before I start to write this formula, I want to make life a little bit simpler, I'm going to just go to those answers there, I'm going to give it a name of “Pro” as in product and these answers here, I'm going to give that a name of “Pack”, and just because I’m lazy I’m going to do equal one minus this do this work here in this table and then we're going to name this whole range to be discount “Disc” for discounts.
We have pro, we have pack, we have discount, let's our disc, let's go on here.
Okay so three components.
First component is that we need the cost based on the product so I want to use the INDEX of my Pro range comma and which one we want, we want the right this F4 1,2,3x comma 1.
Okay, why did I press F4 three times?
Because I want to lock this down to column eight, see the dollar sign before the H, that's saying that we're locking it down, now this is going to give me, in this case, the text number one, the text number two, the text number three, text number four, the text number five, and you would say well hey wait you can't use that text there, but I tested it, and sure enough it works so here let's just take a quick look at that and it is pulling the correct product cost that surprised me, I feared we have to multiply that by one or something like that to coerce it into a value but it turns out that I do not have to do that, so that's our first look up I’m going to add the product costs based on the pack so I'm going to use again the INDEX of our Pack name and that's going to be the right of this cell comma 1, alright so now I have the price of the Pro, the product and the price of the pack.
Of course this I’m going to have to put a dollar sign right there before the two, I have to press the F4 twice but I forgot to do that.
Alright so we now have these two items being added up and we need to multiply it times the discount, times the discount, now you know these numbers here 25, 50, 61, 43, 29 are not in my discount table but luckily the discount table is sorted so that means that we're going to be able to use the comma true version of VLOOKUP, so all right here we're going to multiply that whole sum times VLOOKUP of this value, press F4 one, two, three times to lock it down to the column and our table name was called Disc comma, we got the third column and True, we don't have to put True in, we could just leave it off, I always put True in there just to remind myself that this is the range type of lookup.
Okay what if we really want to have that out to three decimal places probably not but you didn't say it but I'm going to do a ROUND comma 2 to make sure that is rounding correctly.
All right there you go I think that's it, Mike let's see what you have.
Mike from Excel Is Fun Thanks MrExcel, man it's great to be back doing duels after not doing from long time, hey this formula totally cool looking at the right 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5 that is beautiful INDEX right, VLOOKUP, I’m going to go ahead and do, just do, 3 VLOOKUP’s and assume that maybe in some situations you don't have those numbers there so over here now I have the tables over here I’m actually going to name this sheet tables two I and then use these let me do the same thing MrExcel did but since I have, I’m going to use three VLOOKUP’s, I need the table, table, table so I'm just going to come up to the name box and call this ProductT enter, how about PackT, PackT for package table and then DiscountT.
Alright, so I got those names and I can come over here, yeah we have to add the actual cost and the package and then multiply it times the discount.
So I'm going to use this SUM function, how about that, and then just go number, number and have a VLOOKUP in each, so I'm going to say VLOOKUP, lookup value, I'll do the product first and I'm going to hit the F4, just like MrExcel, that column is locked so when I drag it this way it's locked there but when I drag it down those little dancing ants will move down, comma within the product table, look it's all the way down at the bottom, comma 2, because the thing will return in the second column, and I'm going to do exact match because we I'm assuming that you might have different names and they're not sorted all right and now that's just sitting right there in the number argument so I type a comma and I’m going to do VL of this and this one needs to be F4 two times lock the row reference those you copy it down it's locked down but when you move over this way those dancing ants will move over to the next package name, comma it looks like we have a packT same thing second column has some more retrieving an exact match all right so that second VLOOKUP’s in the number I just closed parenthesis now that SUM will add both of them.
Now I’m going to go ahead and do one more times now my table didn't have one minus the discount and in order to take that cost and get the cost you're actually paying minus the discount I’m going to say in parenthesis 1 minus and then the VLOOKUP and so the VLOOKUP will be here, the quantity same thing 3f force to lock the column but not the row comma within DiscountT, DiscT, comma and this is two, second column at something we want to retrieve and I'm leaving it off because the default is approximate match all right now that will give me the price all the way through just like that now I’m going to assume that maybe we want total cost, notice I have highlighted, I mean I’ve copied this over so the whole table is highlighted, the active cell right there I’m just going to hit F2 to put it in edit mode and I’m going to multiply that amount times the quantity now this will have to also have the F4 one, two, three times column but not the row locked, now to my edited formula in the active cell I want to send it into all of the highlighted cells I hold ctrl and enter that tells the edited formula to please repopulate into all of the selected cells.
F2 again and let's do a ROUND around that comma two, close parenthesis, control enter.
All right throwback to MrExcel.
Bill from MrExcel All right Mike that was cool 3v lookups one of which was true and then nice trick there at the end with the ctrl-enter well.
Hey I want to thank everyone for stopping by I will see you next week for another duelling excel podcast from MrExcel and Excel Is Fun.
Okay today's question from Judy at YouTube, I have listed with five product choices, another list with six packaging choices and a list that shows the discount and for each of these cells we need to look up those tables.
Let's take a look at a table, so here's our product table, here's our packaging table and then our discount table.
Now before I start to write this formula, I want to make life a little bit simpler, I'm going to just go to those answers there, I'm going to give it a name of “Pro” as in product and these answers here, I'm going to give that a name of “Pack”, and just because I’m lazy I’m going to do equal one minus this do this work here in this table and then we're going to name this whole range to be discount “Disc” for discounts.
We have pro, we have pack, we have discount, let's our disc, let's go on here.
Okay so three components.
First component is that we need the cost based on the product so I want to use the INDEX of my Pro range comma and which one we want, we want the right this F4 1,2,3x comma 1.
Okay, why did I press F4 three times?
Because I want to lock this down to column eight, see the dollar sign before the H, that's saying that we're locking it down, now this is going to give me, in this case, the text number one, the text number two, the text number three, text number four, the text number five, and you would say well hey wait you can't use that text there, but I tested it, and sure enough it works so here let's just take a quick look at that and it is pulling the correct product cost that surprised me, I feared we have to multiply that by one or something like that to coerce it into a value but it turns out that I do not have to do that, so that's our first look up I’m going to add the product costs based on the pack so I'm going to use again the INDEX of our Pack name and that's going to be the right of this cell comma 1, alright so now I have the price of the Pro, the product and the price of the pack.
Of course this I’m going to have to put a dollar sign right there before the two, I have to press the F4 twice but I forgot to do that.
Alright so we now have these two items being added up and we need to multiply it times the discount, times the discount, now you know these numbers here 25, 50, 61, 43, 29 are not in my discount table but luckily the discount table is sorted so that means that we're going to be able to use the comma true version of VLOOKUP, so all right here we're going to multiply that whole sum times VLOOKUP of this value, press F4 one, two, three times to lock it down to the column and our table name was called Disc comma, we got the third column and True, we don't have to put True in, we could just leave it off, I always put True in there just to remind myself that this is the range type of lookup.
Okay what if we really want to have that out to three decimal places probably not but you didn't say it but I'm going to do a ROUND comma 2 to make sure that is rounding correctly.
All right there you go I think that's it, Mike let's see what you have.
Mike from Excel Is Fun Thanks MrExcel, man it's great to be back doing duels after not doing from long time, hey this formula totally cool looking at the right 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5 that is beautiful INDEX right, VLOOKUP, I’m going to go ahead and do, just do, 3 VLOOKUP’s and assume that maybe in some situations you don't have those numbers there so over here now I have the tables over here I’m actually going to name this sheet tables two I and then use these let me do the same thing MrExcel did but since I have, I’m going to use three VLOOKUP’s, I need the table, table, table so I'm just going to come up to the name box and call this ProductT enter, how about PackT, PackT for package table and then DiscountT.
Alright, so I got those names and I can come over here, yeah we have to add the actual cost and the package and then multiply it times the discount.
So I'm going to use this SUM function, how about that, and then just go number, number and have a VLOOKUP in each, so I'm going to say VLOOKUP, lookup value, I'll do the product first and I'm going to hit the F4, just like MrExcel, that column is locked so when I drag it this way it's locked there but when I drag it down those little dancing ants will move down, comma within the product table, look it's all the way down at the bottom, comma 2, because the thing will return in the second column, and I'm going to do exact match because we I'm assuming that you might have different names and they're not sorted all right and now that's just sitting right there in the number argument so I type a comma and I’m going to do VL of this and this one needs to be F4 two times lock the row reference those you copy it down it's locked down but when you move over this way those dancing ants will move over to the next package name, comma it looks like we have a packT same thing second column has some more retrieving an exact match all right so that second VLOOKUP’s in the number I just closed parenthesis now that SUM will add both of them.
Now I’m going to go ahead and do one more times now my table didn't have one minus the discount and in order to take that cost and get the cost you're actually paying minus the discount I’m going to say in parenthesis 1 minus and then the VLOOKUP and so the VLOOKUP will be here, the quantity same thing 3f force to lock the column but not the row comma within DiscountT, DiscT, comma and this is two, second column at something we want to retrieve and I'm leaving it off because the default is approximate match all right now that will give me the price all the way through just like that now I’m going to assume that maybe we want total cost, notice I have highlighted, I mean I’ve copied this over so the whole table is highlighted, the active cell right there I’m just going to hit F2 to put it in edit mode and I’m going to multiply that amount times the quantity now this will have to also have the F4 one, two, three times column but not the row locked, now to my edited formula in the active cell I want to send it into all of the highlighted cells I hold ctrl and enter that tells the edited formula to please repopulate into all of the selected cells.
F2 again and let's do a ROUND around that comma two, close parenthesis, control enter.
All right throwback to MrExcel.
Bill from MrExcel All right Mike that was cool 3v lookups one of which was true and then nice trick there at the end with the ctrl-enter well.
Hey I want to thank everyone for stopping by I will see you next week for another duelling excel podcast from MrExcel and Excel Is Fun.
