Today's dueling Excel podcast question is how to return the sum of all columns from a VLOOKUP. Mike and Bill show you various methods in episode 1161.
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 Friday, that means it's time for another Dueling Excel podcast, I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun will be joining us.
Mike sent me this question, he says “Alright, someone wants to do a lookup, Product 4, when they find Product 4, they want to multiply all of those numbers, 8*9*5*8*4* the quantity.” Alright now, Mike said this be a great duel, I can only think of one way to do it: =SUMPRODUCT.
So SUMPRODUCT is going to multiply a whole bunch of stuff together, the stuff in this case is going to use the OFFSET function, so OFFSET is very powerful.
We're going to start from this top-left corner cell right there, press the F4 key, it says how many rows down we want to do.
Well OK, to get the number of rows down I'm going to use the MATCH.
Go find this value over here, the Product 4, within this range of products, I'll press F4 there, and ,0 to force an exact match, that'll tell me how many rows to go down.
Next, how many columns to go over, well, that's 0, how many rows tall do I want, that's 1, how many columns wide do I want, that's 5.
Alright, so we have to close the OFFSET, close the SUMPRODUCT, press Enter, that's the 34, good!
And then times the number to the left of me.
Oh shoot, you know, I'm in something called Edit mode here, I want to be in Point mode, so I'm going to press the F2 key, and I can just hit the left arrow, there we go.
And the answer should be the desired 170, we'll try it down here, you know, those comments are driving me insane.
Let's undo, we will copy, and then Paste Special Formulas, there we go, looks like it's working, and then over here Paste Special Formulas, and here Paste Special Formulas, there we go.
Alright Mike, let's see what you got!
Mike: Thanks MrExcel!
Hey, this is a great problem, this actually was sent in from abstraktus from YouTube, he's a cake manufacturer, and I actually already did a video on this topic.
Ah, but there's so many cool ways to do this, and of course, I suggested this to MrExcel for a duel.
Alright, so the way I'm going to do this, here's our products, the quantity, and we need a total difficulty score, so 5* the whole row for product 4 in this lookup table.
Now, I'm going to highlight all of the cells that should get this formula right up front, and I've highlighted that column, now I'm going to hold Ctrl and then highlight this one.
I'm aware of where the active cell is, because when I do more than one cell formula at one time, you got to build the formula from the point of view of the active cell.
So here we go, I'm just going to use VLOOKUP, right?
VLOOKUP will just look up this product, right, so I'll look up this product, that'll be a relative cell reference.
By the way I've highlighted them all, so as soon as I finish the formula, I just Ctrl+Enter, and it populates all of those cells.
The table array, I'll just get this whole table right here.
The lookup column is going to be the first column, of course, that's the way VLOOKUP works.
So I'm going to hit the F4 key, and then comma- Oops, column index?
But wait a second, 2-3-4-5 and a 6, we need all five columns, 2-6!
So I'm just going to put {2,3,4,5,6}.
Wow, this is an array built right into the formula, and as soon as we do that, this becomes an array formula, because it's going to return all of the 5 numbers, so it's returning more than one number, so it's an array.
Comma, and this is an exact match so I'm going to do 0 for exact match, close parentheses.
Now to see that this work, I'm going to actually highlight this and hit the F9 key, that evaluates it, 89584, so it looks like it's working, I'm going to Ctrl+Z.
Now, that's an array of values, we want to put this in SUMPRODUCT, just like MrExcel did, array1.
And then I'm going to comma and array2, well, I just want this.
Ah, but SUMPRODUCT will not allow arrays of different dimensions.
This first look up has given us a 1:5, and this cell is a 1:1.
We can get around that, comma, problem, requiring the same dimension by using multiplication, so there we go, close parentheses.
Now I'm going to Ctrl+Enter to populate all the cells, oops.
I want some blanks down here until I fill this in.
With the active cell still selected, I'm going to hit the F2 key, Edit, and I'll just put a little IF.
If two cells to my left equals blank, “” then what do I want?
The screen tips helps me out here, “”, and then the value of FALSE is that big thing right there, so I come to the end, close parentheses.
I hold Ctrl and tap Enter to repopulate all the cells, so now, if I select a Product 2, and we made 43 of them, that should work just fine.
Alright, we'll see you next trick!
Bill: Hey thanks Mike, that is an excellent trick, using 2-3-4-5-6 as an array for the return column, what a cool trick, definitely a point to Mike.
Hey, I want to thank everyone for stopping by, we’ll see you next week for another Dueling podcast from ExcelIsFun and MrExcel!
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 Friday, that means it's time for another Dueling Excel podcast, I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun will be joining us.
Mike sent me this question, he says “Alright, someone wants to do a lookup, Product 4, when they find Product 4, they want to multiply all of those numbers, 8*9*5*8*4* the quantity.” Alright now, Mike said this be a great duel, I can only think of one way to do it: =SUMPRODUCT.
So SUMPRODUCT is going to multiply a whole bunch of stuff together, the stuff in this case is going to use the OFFSET function, so OFFSET is very powerful.
We're going to start from this top-left corner cell right there, press the F4 key, it says how many rows down we want to do.
Well OK, to get the number of rows down I'm going to use the MATCH.
Go find this value over here, the Product 4, within this range of products, I'll press F4 there, and ,0 to force an exact match, that'll tell me how many rows to go down.
Next, how many columns to go over, well, that's 0, how many rows tall do I want, that's 1, how many columns wide do I want, that's 5.
Alright, so we have to close the OFFSET, close the SUMPRODUCT, press Enter, that's the 34, good!
And then times the number to the left of me.
Oh shoot, you know, I'm in something called Edit mode here, I want to be in Point mode, so I'm going to press the F2 key, and I can just hit the left arrow, there we go.
And the answer should be the desired 170, we'll try it down here, you know, those comments are driving me insane.
Let's undo, we will copy, and then Paste Special Formulas, there we go, looks like it's working, and then over here Paste Special Formulas, and here Paste Special Formulas, there we go.
Alright Mike, let's see what you got!
Mike: Thanks MrExcel!
Hey, this is a great problem, this actually was sent in from abstraktus from YouTube, he's a cake manufacturer, and I actually already did a video on this topic.
Ah, but there's so many cool ways to do this, and of course, I suggested this to MrExcel for a duel.
Alright, so the way I'm going to do this, here's our products, the quantity, and we need a total difficulty score, so 5* the whole row for product 4 in this lookup table.
Now, I'm going to highlight all of the cells that should get this formula right up front, and I've highlighted that column, now I'm going to hold Ctrl and then highlight this one.
I'm aware of where the active cell is, because when I do more than one cell formula at one time, you got to build the formula from the point of view of the active cell.
So here we go, I'm just going to use VLOOKUP, right?
VLOOKUP will just look up this product, right, so I'll look up this product, that'll be a relative cell reference.
By the way I've highlighted them all, so as soon as I finish the formula, I just Ctrl+Enter, and it populates all of those cells.
The table array, I'll just get this whole table right here.
The lookup column is going to be the first column, of course, that's the way VLOOKUP works.
So I'm going to hit the F4 key, and then comma- Oops, column index?
But wait a second, 2-3-4-5 and a 6, we need all five columns, 2-6!
So I'm just going to put {2,3,4,5,6}.
Wow, this is an array built right into the formula, and as soon as we do that, this becomes an array formula, because it's going to return all of the 5 numbers, so it's returning more than one number, so it's an array.
Comma, and this is an exact match so I'm going to do 0 for exact match, close parentheses.
Now to see that this work, I'm going to actually highlight this and hit the F9 key, that evaluates it, 89584, so it looks like it's working, I'm going to Ctrl+Z.
Now, that's an array of values, we want to put this in SUMPRODUCT, just like MrExcel did, array1.
And then I'm going to comma and array2, well, I just want this.
Ah, but SUMPRODUCT will not allow arrays of different dimensions.
This first look up has given us a 1:5, and this cell is a 1:1.
We can get around that, comma, problem, requiring the same dimension by using multiplication, so there we go, close parentheses.
Now I'm going to Ctrl+Enter to populate all the cells, oops.
I want some blanks down here until I fill this in.
With the active cell still selected, I'm going to hit the F2 key, Edit, and I'll just put a little IF.
If two cells to my left equals blank, “” then what do I want?
The screen tips helps me out here, “”, and then the value of FALSE is that big thing right there, so I come to the end, close parentheses.
I hold Ctrl and tap Enter to repopulate all the cells, so now, if I select a Product 2, and we made 43 of them, that should work just fine.
Alright, we'll see you next trick!
Bill: Hey thanks Mike, that is an excellent trick, using 2-3-4-5-6 as an array for the return column, what a cool trick, definitely a point to Mike.
Hey, I want to thank everyone for stopping by, we’ll see you next week for another Dueling podcast from ExcelIsFun and MrExcel!
