Fabian asks if a VLOOKUP can sum all of the values in the matching row. This video looks at how to solve the problem using the "Address" version of INDEX. Also - a plug for my upcoming seminars in March 2016 in Australia and New Zealand
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1971. VLOOKUP to SUM The Entire Matching Row.
Well hey, I just finished doing a seminar, for the Australia region of Excel for Apps.
Now, I'm pretty excited.
If you happen to be watching this here in February, I'm going to be heading down to do my very first seminars in New Zealand and Australia, March 3rd through 11th.
If you're in Auckland, or Sydney, or Melbourne, or can get there, you'll want to check this out: excelsummitsouth.wordpress.com ! A whole bunch of people, Charles Williams from the UK, John Peltier, Ken Pools(?).
A whole bunch of Excel people, will be doing two days of seminars in each city.
And so I was, doing the seminar - webinar in advance of that, talking about VLOOKUP, and Fabian asked this brilliant question.
He says "Look, I want to do a VLOOKUP here for A102, and when I find the match, I want to add up all of the numbers in that row!" OK, and this is going to be really, I'm going to say, tricky, or clever, I don't know.
First thing I'm going to do is, I'm going to do a match, to figure out where A102 is in the list.
=MATCH() A102, and there's our set of answers, and want an exact match, so ,0, which is like doing a comma false at the end of VLOOKUP.
And A102 is the second item of that list.
But as we copy that down, it's going to be in other places.
Press that 4 there, double click to copy that down.
Right so, gets rid of those #N/As! Alright so, that tells us where the answer is going to be, and I'm going to use something very unusual here, I'm going to do something called: =INDEX() of the January column.
Press F4 two times, to lock it down to the row, and what do I want, I want the second item in that range of values.
Normally if I would just press Enter here, the index is going to return a 3, because that's the value found for A102, but I'm not going to just press Enter here, I'm going to put a : , alright, and this is a very unusual use of INDEX.
When you follow an INDEX, or precede an index with a : , instead of returning the value that's found there, what it's going to return is the cell address.
It's actually going to say that this is L5 where it's found.
And so, that I'm going to ask for the index of all of the December values, the last column, press F4 twice.
And what do we want, we want the answer from column B, alright.
And this amazing function is going to actually say: Hey, we want everything from L5 to W5! Now in the past, I would have done this with an offset function, but offset?
Yeah, its volatile function, it's a very, very slow function.
INDEX does not suffer from this problem.
Since this is returning 12 cells, I'm going to pass it to the SUM function, and ) .
I don't need CTRL+Shift+Enter, I just enter the answer, double-click to shoot that down.
Let's do a little test here, to make sure that this is working! So we pulled these values here from A102, and sure enough 46, alright, so.
Beautiful, beautiful little formula there, using this really strange use of INDEX that has a colon after it, or before it, in order to force those to change into actual cell addresses.
Here, to show you what I mean, I'll choose that cell.
We'll do Evaluate Formula, and we'll evaluate right here.
This is where it's about to do the INDEX, and normally it would return the value that's there, but because it has a colon after it, it actually returns the cell address.
And here again, it returns it, so.
It's actually those indexes are changing into cell addresses, really, really cool use.
I learned that from Dan Mayo(?), who was on the question team for the model off group, Dan showed me that at one of the former model off world modeling championships, and I was like, THAT is a clever, clever use.
Hey, I want to thank Fabian from Australia for sending that question in, I want to thank you for stopping by, and if you're in Australia or New Zealand, I hope to see you here, in a couple of weeks, so when I come down for the Excel Summit South.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well hey, I just finished doing a seminar, for the Australia region of Excel for Apps.
Now, I'm pretty excited.
If you happen to be watching this here in February, I'm going to be heading down to do my very first seminars in New Zealand and Australia, March 3rd through 11th.
If you're in Auckland, or Sydney, or Melbourne, or can get there, you'll want to check this out: excelsummitsouth.wordpress.com ! A whole bunch of people, Charles Williams from the UK, John Peltier, Ken Pools(?).
A whole bunch of Excel people, will be doing two days of seminars in each city.
And so I was, doing the seminar - webinar in advance of that, talking about VLOOKUP, and Fabian asked this brilliant question.
He says "Look, I want to do a VLOOKUP here for A102, and when I find the match, I want to add up all of the numbers in that row!" OK, and this is going to be really, I'm going to say, tricky, or clever, I don't know.
First thing I'm going to do is, I'm going to do a match, to figure out where A102 is in the list.
=MATCH() A102, and there's our set of answers, and want an exact match, so ,0, which is like doing a comma false at the end of VLOOKUP.
And A102 is the second item of that list.
But as we copy that down, it's going to be in other places.
Press that 4 there, double click to copy that down.
Right so, gets rid of those #N/As! Alright so, that tells us where the answer is going to be, and I'm going to use something very unusual here, I'm going to do something called: =INDEX() of the January column.
Press F4 two times, to lock it down to the row, and what do I want, I want the second item in that range of values.
Normally if I would just press Enter here, the index is going to return a 3, because that's the value found for A102, but I'm not going to just press Enter here, I'm going to put a : , alright, and this is a very unusual use of INDEX.
When you follow an INDEX, or precede an index with a : , instead of returning the value that's found there, what it's going to return is the cell address.
It's actually going to say that this is L5 where it's found.
And so, that I'm going to ask for the index of all of the December values, the last column, press F4 twice.
And what do we want, we want the answer from column B, alright.
And this amazing function is going to actually say: Hey, we want everything from L5 to W5! Now in the past, I would have done this with an offset function, but offset?
Yeah, its volatile function, it's a very, very slow function.
INDEX does not suffer from this problem.
Since this is returning 12 cells, I'm going to pass it to the SUM function, and ) .
I don't need CTRL+Shift+Enter, I just enter the answer, double-click to shoot that down.
Let's do a little test here, to make sure that this is working! So we pulled these values here from A102, and sure enough 46, alright, so.
Beautiful, beautiful little formula there, using this really strange use of INDEX that has a colon after it, or before it, in order to force those to change into actual cell addresses.
Here, to show you what I mean, I'll choose that cell.
We'll do Evaluate Formula, and we'll evaluate right here.
This is where it's about to do the INDEX, and normally it would return the value that's there, but because it has a colon after it, it actually returns the cell address.
And here again, it returns it, so.
It's actually those indexes are changing into cell addresses, really, really cool use.
I learned that from Dan Mayo(?), who was on the question team for the model off group, Dan showed me that at one of the former model off world modeling championships, and I was like, THAT is a clever, clever use.
Hey, I want to thank Fabian from Australia for sending that question in, I want to thank you for stopping by, and if you're in Australia or New Zealand, I hope to see you here, in a couple of weeks, so when I come down for the Excel Summit South.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
