Mike and Bill look at two methods to lookup a column of sales amounts in order to create a single cell formula that will add all the bonuses. Learn more about the LOOKUP function, how to use SUM and SUMPRODUCT and Array Formulas in Episode #1513.
Dueling Excel #96 and "The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel #96 and "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 another dueling excel podcast.
I'm Bill Jelen from MrExcel and will be joined by Mike Girvin from Excel Is Fun This is our episode 96.
Total All Lookups.
Hey everyone. This is, we are going to, we're going to go beyond arcane.
We're going to go to arcane to the third power and my kid sent me this one and I know that I'm being baited because he knows that he has a better way to solve this.
He saw this in my book Learn Excel 2007, 2010 from MrExcel, and I'm, I already know, I already know that he's gonna have me beat here, but I'm gonna do it the way that I did in the book, here's the question.
We have a whole bunch of invoices and amounts and here is our bonus table.
We don't want to pay the bonus yet, we just want to create a little accrual reserve to figure out how much the bonus is gonna to be.
So, I wanna know what the total of all the bonuses are in a single formula.
So, let's go arcane to the first level, equal VLOOKUP, go look up this amount over here in this table F4, I want the second column, but I don't want an exact match.
So, I'm not going to put False.
Instead, I would tend to put True, but it turns out you don't need to put true you can just leave it off.
All right so, what that does that looks for 12,835 in this table when it doesn't find it, it goes to the row just before 12,835.
Now, this is the only time that this table has to be sorted, has to be sorted in ascending or most VLOOKUP's, the table does not have to be sorted, but here it does.
All right, double click to shoot that down and we have our formulas.
But hey, wait. I don't want all of these formulas.
I want one formula to add all that up.
Unfortunately, if I try and do equal SUM open parenthesis VLOOKUP, there's nothing I can do, Control+Shift+Enter, nothing will get that to work.
So, we need to go not just arcane, but arcane, arcane. Are you ready?
Equal LOOKUP, LOOKUP. What the heck is that?
Go look in Excel help for LOOKUP it says, "Hey, don't use this there's newer versions of look up.
Go look at VLOOKUP. " But a look up has a trick.
So, look up will do vertical or horizontal.
It will only drew the... Do the true version of you VLOOKUP, and it has this weird, weird thing where you have to say here's the lookup value, 12,835.
Here is the lookup vector, a vector is one column or one row F4 and here is the results vector.
So, you get to specify not on a whole table, but the, the Column you're looking up and then the column that has the results and that should give us the exact same results as that VLOOKUP, over there in column D, but then the question is well, why would you bother to do this, when, VLOOKUP does it.
Well, because as I said VLOOKUP cannot be used in an array formula here to sum everything, but this lookup can, I'm gonna go into the next worksheet here and so, here's my formula.
I want to look up all of the values from C2 to C26 have to look up vector as E2 to E6, the results vector is F2 to F6, wrap that whole thing in a sum, hold down Control+Shift and press Enter.
652, Let's just do a quick check here, equal SUM of my results here in the original lookup, 652, it works!
Isn't that crazy? This whole this old, old, old, old.
Did I say old? Old lookup function, that Microsoft even tells you not to use, has a cool trick and it can be used to look up a range of values.
Control+Shift+Enter will return that to the SUM function and if we get our totals, pretty wild. I thought it was cool.
I put it in the new edition of the book, but Mike I have this a seeking suspicion, is going to have a better way to do this. Mike.
Mike: Thanks MrExcel.
Beat you, I don't think so.
Now, this formula is totally awesome. I have done, look up adding with an exact match where you sum it in some product, but I actually had never seen this.
This is an approximate match, look up adding.
Now, this formulas totally cool and I gotta tell you something about his new book.
Here's his new book. This is the...
Learn Excel form MrExcel with 512.
I used to use his book before I started making videos, the one with 277 tricks.
This is way back.
I used to use this in my advanced Excel class and he came out with an intermediate version, but this one has 512 tricks and this is one of the new tricks.
Now, if you have some of his older books, this book has a bunch of cool new tricks.
All right! So, I like this. In particular, the lookup vector and the result vector with the LOOKUP function.
Now, it happens to be that both columns are next to each other now, this form of it would be great.
If this range right here was somewhere else or even transposed, then you could use this version here and like MrExcel said, "LOOKUP, it is old." I think you said old a lot of times, no way!
There's lots of great uses for look up because it can handle arrays in various ways where VLOOKUP can't.
All right, I'm going to come over here straight still in the idea. I'm just gonna, instead of you SUM, I'm going to use SUMPRODUCT.
Now, SUMPRODUCT is great because it can handle arrays and that LOOKUP function is returning an array of values.
SUMPRODUCT just allows me to use the lookup without using Control+Shift+Enter.
I'm going to use that LOOKUP, totally awesome!
Lookup value all of these Control+Shift+Down arrow, and here's the thing about LOOKUP. This...
There's the lookup value, right? It's a bunch of stuff, this lookup vector.
It can either do horizontal or vertical look up.
Now, since the table is taller than its wider, it's gonna...
If you just use this one argument, it will do VLOOKUP, if it was wider than that, it's taller it would do horizontal lookup.
So, for the lookup vector, I just highlight this.
Now, the look is programmed to always take the value from the last column and return it.
All right! So, then I don't have to use those, the result vector in this only because they're next to each other, close parenthesis, close parenthesis, and Enter.
652.
All right, I'm given the point to MrExcel for this awesome new 200 plus new tricks.
All right, see you next video.
Bill: Hey, all right Mike. I'm glad you liked that trick.
You know I, I'm a big fan of VLOOKUP, and you know, if we were doing VLOOKUP's into this table, we could just specify this as the entire table range, press F4 and whether we wanted column 2, 3 or 4.
We wouldn't have to think about it.
It always irritates me the way that, that Mike uses that formula, where you specify a range, but the value that you want to return is the rightmost edge of the range.
Although, I guess it really isn't that bad because I can lock this range down say, "Hey, it's always going to start an E," but as we copy to the right it's going to get one column wider and so, copy that across, see now it goes to G and returns the value from G and copy it one more, now it goes over to H and returns the value from H.
So, it may not be as strange and arcane as I thought so, but definitely kind of out of my element here.
All right, you know VLOOKUP's, I've done millions of my life.
Look up, probably done about 10 or so, so...
still given the point to Mike, plus throwing this on product in there.
All right! Hey, I wanna thank everyone for stopping by.
We'll see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel and will be joined by Mike Girvin from Excel Is Fun This is our episode 96.
Total All Lookups.
Hey everyone. This is, we are going to, we're going to go beyond arcane.
We're going to go to arcane to the third power and my kid sent me this one and I know that I'm being baited because he knows that he has a better way to solve this.
He saw this in my book Learn Excel 2007, 2010 from MrExcel, and I'm, I already know, I already know that he's gonna have me beat here, but I'm gonna do it the way that I did in the book, here's the question.
We have a whole bunch of invoices and amounts and here is our bonus table.
We don't want to pay the bonus yet, we just want to create a little accrual reserve to figure out how much the bonus is gonna to be.
So, I wanna know what the total of all the bonuses are in a single formula.
So, let's go arcane to the first level, equal VLOOKUP, go look up this amount over here in this table F4, I want the second column, but I don't want an exact match.
So, I'm not going to put False.
Instead, I would tend to put True, but it turns out you don't need to put true you can just leave it off.
All right so, what that does that looks for 12,835 in this table when it doesn't find it, it goes to the row just before 12,835.
Now, this is the only time that this table has to be sorted, has to be sorted in ascending or most VLOOKUP's, the table does not have to be sorted, but here it does.
All right, double click to shoot that down and we have our formulas.
But hey, wait. I don't want all of these formulas.
I want one formula to add all that up.
Unfortunately, if I try and do equal SUM open parenthesis VLOOKUP, there's nothing I can do, Control+Shift+Enter, nothing will get that to work.
So, we need to go not just arcane, but arcane, arcane. Are you ready?
Equal LOOKUP, LOOKUP. What the heck is that?
Go look in Excel help for LOOKUP it says, "Hey, don't use this there's newer versions of look up.
Go look at VLOOKUP. " But a look up has a trick.
So, look up will do vertical or horizontal.
It will only drew the... Do the true version of you VLOOKUP, and it has this weird, weird thing where you have to say here's the lookup value, 12,835.
Here is the lookup vector, a vector is one column or one row F4 and here is the results vector.
So, you get to specify not on a whole table, but the, the Column you're looking up and then the column that has the results and that should give us the exact same results as that VLOOKUP, over there in column D, but then the question is well, why would you bother to do this, when, VLOOKUP does it.
Well, because as I said VLOOKUP cannot be used in an array formula here to sum everything, but this lookup can, I'm gonna go into the next worksheet here and so, here's my formula.
I want to look up all of the values from C2 to C26 have to look up vector as E2 to E6, the results vector is F2 to F6, wrap that whole thing in a sum, hold down Control+Shift and press Enter.
652, Let's just do a quick check here, equal SUM of my results here in the original lookup, 652, it works!
Isn't that crazy? This whole this old, old, old, old.
Did I say old? Old lookup function, that Microsoft even tells you not to use, has a cool trick and it can be used to look up a range of values.
Control+Shift+Enter will return that to the SUM function and if we get our totals, pretty wild. I thought it was cool.
I put it in the new edition of the book, but Mike I have this a seeking suspicion, is going to have a better way to do this. Mike.
Mike: Thanks MrExcel.
Beat you, I don't think so.
Now, this formula is totally awesome. I have done, look up adding with an exact match where you sum it in some product, but I actually had never seen this.
This is an approximate match, look up adding.
Now, this formulas totally cool and I gotta tell you something about his new book.
Here's his new book. This is the...
Learn Excel form MrExcel with 512.
I used to use his book before I started making videos, the one with 277 tricks.
This is way back.
I used to use this in my advanced Excel class and he came out with an intermediate version, but this one has 512 tricks and this is one of the new tricks.
Now, if you have some of his older books, this book has a bunch of cool new tricks.
All right! So, I like this. In particular, the lookup vector and the result vector with the LOOKUP function.
Now, it happens to be that both columns are next to each other now, this form of it would be great.
If this range right here was somewhere else or even transposed, then you could use this version here and like MrExcel said, "LOOKUP, it is old." I think you said old a lot of times, no way!
There's lots of great uses for look up because it can handle arrays in various ways where VLOOKUP can't.
All right, I'm going to come over here straight still in the idea. I'm just gonna, instead of you SUM, I'm going to use SUMPRODUCT.
Now, SUMPRODUCT is great because it can handle arrays and that LOOKUP function is returning an array of values.
SUMPRODUCT just allows me to use the lookup without using Control+Shift+Enter.
I'm going to use that LOOKUP, totally awesome!
Lookup value all of these Control+Shift+Down arrow, and here's the thing about LOOKUP. This...
There's the lookup value, right? It's a bunch of stuff, this lookup vector.
It can either do horizontal or vertical look up.
Now, since the table is taller than its wider, it's gonna...
If you just use this one argument, it will do VLOOKUP, if it was wider than that, it's taller it would do horizontal lookup.
So, for the lookup vector, I just highlight this.
Now, the look is programmed to always take the value from the last column and return it.
All right! So, then I don't have to use those, the result vector in this only because they're next to each other, close parenthesis, close parenthesis, and Enter.
652.
All right, I'm given the point to MrExcel for this awesome new 200 plus new tricks.
All right, see you next video.
Bill: Hey, all right Mike. I'm glad you liked that trick.
You know I, I'm a big fan of VLOOKUP, and you know, if we were doing VLOOKUP's into this table, we could just specify this as the entire table range, press F4 and whether we wanted column 2, 3 or 4.
We wouldn't have to think about it.
It always irritates me the way that, that Mike uses that formula, where you specify a range, but the value that you want to return is the rightmost edge of the range.
Although, I guess it really isn't that bad because I can lock this range down say, "Hey, it's always going to start an E," but as we copy to the right it's going to get one column wider and so, copy that across, see now it goes to G and returns the value from G and copy it one more, now it goes over to H and returns the value from H.
So, it may not be as strange and arcane as I thought so, but definitely kind of out of my element here.
All right, you know VLOOKUP's, I've done millions of my life.
Look up, probably done about 10 or so, so...
still given the point to Mike, plus throwing this on product in there.
All right! Hey, I wanna thank everyone for stopping by.
We'll see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
