On Sheet 1, you have customer data with subtotals added by the Subtotal command. On Sheet 2, you have a list of customers. How do you do VLOOKUP from Sheet 2 to get the total for each customer from Sheet 1?
This question was posed during my Fort Wayne seminar. In episode 1882, two possible ways to solve the problem.
This question was posed during my Fort Wayne seminar. In episode 1882, two possible ways to solve the problem.
Transcript of the video:
The MrExcel Podcast is sponsored by Easy-XL.
Learn Exel from MrExcel podcast. Episode 1882 - VLOOKUP into Subtotal data Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Another question from the Fort Wayne seminar.
So we have data here that has been subtotaled right using data subtotals in each changing customer has subtotals here.
And then we have another sheet with a list of customers and we have two VLOOKUP from this sheet back into the other sheet.
Let's see if we get these side-by-side New Window View Arrange All Vertical and then we can see what the data looks like and two methods all right so my first method is I'm thinking about this you have AIG and the words AIG Total is not to use a VLOOKUP at all we can actually use =SUMIF Someone have said if the sum range is over here in column F comma and then we want to look through column D to see if it is equal to this customer here and by using SUMIFs we will get the right answer all the way down.
So my first inclination was not to do a VLOOKUP at all but if we really want to do a VLOOKUP then =VLOOKUP of this customer concatenated with a space and the word Total into outside columns D E and F ,3,False and we get the same answers all right. Someone said well hey, Why don't you just collapse this down to the number to view and select visible cells and copy well you know because in real life sometimes you know we have to populate data into a report that's already there, and you know lots of reasons so a couple of different ways to go here to get a VLOOKUP into subtotal data.
The first one is not to use VLOOKUP at all just to use SUMIFs and count on the fact that the subtotal rows have the word total.
The other one to actually concatenate the space Total after the customer name and that will grab the totals from the subtotal rows.
Two very different ways to go.
I thought that was a great question coming out of the power excel seminar in Fort Wayne.
I'll be in Tampa Florida the next week May 9th for another power excel seminar.
Hopefully we'll get lots of more good tips and questions coming out of that seminar Which ofcourse will make it to the podcast in case you can't be there.
Well, hey I want to thank you for stopping by.Will see you next time for other netcast from MrExcel.
Learn Exel from MrExcel podcast. Episode 1882 - VLOOKUP into Subtotal data Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Another question from the Fort Wayne seminar.
So we have data here that has been subtotaled right using data subtotals in each changing customer has subtotals here.
And then we have another sheet with a list of customers and we have two VLOOKUP from this sheet back into the other sheet.
Let's see if we get these side-by-side New Window View Arrange All Vertical and then we can see what the data looks like and two methods all right so my first method is I'm thinking about this you have AIG and the words AIG Total is not to use a VLOOKUP at all we can actually use =SUMIF Someone have said if the sum range is over here in column F comma and then we want to look through column D to see if it is equal to this customer here and by using SUMIFs we will get the right answer all the way down.
So my first inclination was not to do a VLOOKUP at all but if we really want to do a VLOOKUP then =VLOOKUP of this customer concatenated with a space and the word Total into outside columns D E and F ,3,False and we get the same answers all right. Someone said well hey, Why don't you just collapse this down to the number to view and select visible cells and copy well you know because in real life sometimes you know we have to populate data into a report that's already there, and you know lots of reasons so a couple of different ways to go here to get a VLOOKUP into subtotal data.
The first one is not to use VLOOKUP at all just to use SUMIFs and count on the fact that the subtotal rows have the word total.
The other one to actually concatenate the space Total after the customer name and that will grab the totals from the subtotal rows.
Two very different ways to go.
I thought that was a great question coming out of the power excel seminar in Fort Wayne.
I'll be in Tampa Florida the next week May 9th for another power excel seminar.
Hopefully we'll get lots of more good tips and questions coming out of that seminar Which ofcourse will make it to the podcast in case you can't be there.
Well, hey I want to thank you for stopping by.Will see you next time for other netcast from MrExcel.
