By now, we should know to expect the unusual with the "Learn Excel from MrExcel" Podcast Series...Richard wants to use a VLOOKUP to to find two Values, but Bill thinks that there is a better way to achieve the result using =SUMIFs. Following along with Episode #1642, see how MrExcel uses =SUMIFs to show us a more efficient means than using VLOOKUP for this task.
...This blog is the video podcast companion to the book, 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"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, 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"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1642: VLOOKUP from two values.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by Richard.
Richard sent in an xlsx file where he's trying to do a VLOOKUP for two different values.
So he's saying, “Hey look, I need to match both the country in column A and the crop in column B. Can I do a VLOOKUP for that?
Well, okay you could.
You could join column A and B and then you know do a CONCATENATE LOOKUP but you know they're not particularly sorted.
So the thing we did last Monday with the OFFSET you know that's not going to work.
Since we're in Excel 2007 which I'm assuming because it's an xlsx file 2007 or newer, why don't we just use =SUMIFS( the plural version because that lets us add up numbers based on multiple conditions.
All right, so this is area harvested.
The highest for area harvested are here.
I want to press F4 one, two times to lock down the rows but not the columns that we want to copy over to production.
Everything will work great Then criteria range one.
All right, so I want to look through all of the countries here.
It has to be the same size and shape as the first area F4.
I want to make sure that is equal to whatever country is chosen here in H2.
I'll press F4 comma and then the next criteria range is the crops here in column B F4 comma and make sure that it is equal to Maize.
Here I'll press F4 one, two, three times to lock down just the column.
So here's our formula up here.
=SUMIFS(C$2:C$23,$A$2:$A$23,$H$2,$B$2:$B$23,$G5) We're summing the area harvested based on everything and A being equal to the country everything and B being equal to the crop and what we should get is Ghana maize 991033 and in fact we can copy this to the right 1870614.
Beautiful right.
Double-click to copy this down.
The thing I like about this is if there's no hit at all so Ghana had no beans dry, so now we have 0 instead of #N/A. That's actually a little bit better than VLOOKUP.
We don't have to use IFERROR to change the #N/As to 0.
There was no area harvested, no production.
That works.
The only reason why I can see that this wouldn't be what you wanted is if you were taking advantage of the VLOOKUP only finding the first match but from what I can see from this data, each country and crop is only in there once so SUMIFS would be the way to go.
Oh hey, I want to thank Richard for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1642: VLOOKUP from two values.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by Richard.
Richard sent in an xlsx file where he's trying to do a VLOOKUP for two different values.
So he's saying, “Hey look, I need to match both the country in column A and the crop in column B. Can I do a VLOOKUP for that?
Well, okay you could.
You could join column A and B and then you know do a CONCATENATE LOOKUP but you know they're not particularly sorted.
So the thing we did last Monday with the OFFSET you know that's not going to work.
Since we're in Excel 2007 which I'm assuming because it's an xlsx file 2007 or newer, why don't we just use =SUMIFS( the plural version because that lets us add up numbers based on multiple conditions.
All right, so this is area harvested.
The highest for area harvested are here.
I want to press F4 one, two times to lock down the rows but not the columns that we want to copy over to production.
Everything will work great Then criteria range one.
All right, so I want to look through all of the countries here.
It has to be the same size and shape as the first area F4.
I want to make sure that is equal to whatever country is chosen here in H2.
I'll press F4 comma and then the next criteria range is the crops here in column B F4 comma and make sure that it is equal to Maize.
Here I'll press F4 one, two, three times to lock down just the column.
So here's our formula up here.
=SUMIFS(C$2:C$23,$A$2:$A$23,$H$2,$B$2:$B$23,$G5) We're summing the area harvested based on everything and A being equal to the country everything and B being equal to the crop and what we should get is Ghana maize 991033 and in fact we can copy this to the right 1870614.
Beautiful right.
Double-click to copy this down.
The thing I like about this is if there's no hit at all so Ghana had no beans dry, so now we have 0 instead of #N/A. That's actually a little bit better than VLOOKUP.
We don't have to use IFERROR to change the #N/As to 0.
There was no area harvested, no production.
That works.
The only reason why I can see that this wouldn't be what you wanted is if you were taking advantage of the VLOOKUP only finding the first match but from what I can see from this data, each country and crop is only in there once so SUMIFS would be the way to go.
Oh hey, I want to thank Richard for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
