The dueling Excel podcasts return with a new theme song from Sziliva Juhasz, a new voiceover from Charles "Fast" Williams, a D function from ExcelisFun, and more in this three-way lookup problem.
Transcript of the video:
Hey, welcome back.
It’s time for another Dueling Excel Podcast, I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from Excel is Fun.
This is our Episode 159: It’s another Three-Way Lookup.
All right, today’s question is sent by YouTube: “Is there a way to do a 3-Way Lookup for all the months?” So, we have this Property here, and the section we need to go find that information and then return the right month.
And really important, there are no duplicate records.
All right, while they’re asking for a Lookup, I think that this, because there are no duplicates, can easily be solved with the new =SUMIFS formula.
So, what’s the SUM range?
The SUM range is this range down here =SUMIFS(C$9:C$20) and we’re going to press F4 two times to lock the rows down but not the columns, that way as we drag across it will be adding up a different set of columns.
So, in other words, the third part of the 3-Way Lookup the month will just happen automatically as we copied the columns across.
And then criteria range 1 look through all of these Properties over here and this will be F4 single time, and see if it is equal to the Property.
I’ll press F4 one, two, three times and we always reach back to column A, could have just as easily have been F4 once and then we’ll look through the sections F4 and see if it’s equal to SectionAA =SUMIFS(C$9:C$20,$A$9:$A$20,$A5,$B$9:$B$20,$B5).
Your answer of 165, and there it is highlighted in red.
We’ll copy that across and no need to worry about that third lookup because as we copied across, it automatically points to a new set of columns so here, we’re pulling data from Column D and data from Column E. The important that we didn’t have a $ there before that E, that way the column changes as we copied across.
All right, Mike, let’s see what you got.
Mike Girvin: Thanks, MrExcel.
Hey, =SUMIFS to do this Lookup when there’s no duplicates, I love it.
I also love it because that’s =SUMIFS will be a little bit faster calculating than the Lookup method I’m going to use here.
Hey, I’m going to go straight for the D functions, and I’m going to use the =DGET( function.
That can look something up.
Hey, we have a proper data set right here with field names at the top so we’ll give it the whole data set all the way down, F4 to lock it, comma, the Field that means within this table.
Which Field do we want to get the thing from?
Relative cell reference, comma, and then the criteria because we have Field name in our criteria below; we’re allowed to use this function.
That means it has to find this and that in the same row so I highlight the field names and the criteria F4, close parenthesis that will work =DGET($A$8:$H$20,C4,SA$A:$B$5).
Control Enter and copy it and drag it over.
I go to the last cell F2.
It’s looking good.
Now, if I go and change this to Property1, oh, I get a #VALUE! there because I have to go ahead and change both of them so I’m going to go ahead and add in =IFERROR(DGET($A$8:$H$20,C4,$A$A:$B$5),””).
Ctrl Enter, copy it over.
Now, let’s go ahead and try that again.
Property2 and I’m going to do Property2 section11 and there we go.
Now, I like the =SUMIFS better because the =DGET will calculate a little bit faster.
All right, throw back to MrExcel.
Bill Jelen: Hey, wow, Mike.
I figured you’re going to do a crazy array formula there but =DGET, how old school is that?
=DGET certainly works in this case if mitrammm really just has one set of things for return, but I was afraid that they were going to copy that down and =DGET unless we’re using a data table is not going to work.
Now, let’s see, you said that it will be faster to use =SUMIFS instead of =DGET, let’s see what happens.
Charles Williams: Charles Williams here.
Now, let’s see how those solutions stack up using FastExcel.
Bill Jelen: All right, so here we are on the FastExcel V3.
I went into the menu Calculation Mode and we’re going to try this =SUMIFS or recount the range – 0.182; 0.182 Milliseconds.
We’ll try it a couple of times, 0.225, and Calc Range 0.199.
So, right around 0.2 Milliseconds and then we’ll try the =DGET and see how much slower that is than this =SUMIFS.
Calc Range – ha, hey, 0.358.
So, almost twice as fast or twice as long to calculate – 0.35.
But, really, in this case if there’s just six of them, 0.2 Milliseconds versus 0.345 Milliseconds really, it’s a virtual time although if we had thousands of them, that will be wild.
Michael, I love the =DGET.
It’s great to be back doing Dueling Excel Podcast again.
Thank you everyone for stopping by, we’ll see you next week for Dueling Excel Podcast from MrExcel and Excel is fun.
It’s time for another Dueling Excel Podcast, I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from Excel is Fun.
This is our Episode 159: It’s another Three-Way Lookup.
All right, today’s question is sent by YouTube: “Is there a way to do a 3-Way Lookup for all the months?” So, we have this Property here, and the section we need to go find that information and then return the right month.
And really important, there are no duplicate records.
All right, while they’re asking for a Lookup, I think that this, because there are no duplicates, can easily be solved with the new =SUMIFS formula.
So, what’s the SUM range?
The SUM range is this range down here =SUMIFS(C$9:C$20) and we’re going to press F4 two times to lock the rows down but not the columns, that way as we drag across it will be adding up a different set of columns.
So, in other words, the third part of the 3-Way Lookup the month will just happen automatically as we copied the columns across.
And then criteria range 1 look through all of these Properties over here and this will be F4 single time, and see if it is equal to the Property.
I’ll press F4 one, two, three times and we always reach back to column A, could have just as easily have been F4 once and then we’ll look through the sections F4 and see if it’s equal to SectionAA =SUMIFS(C$9:C$20,$A$9:$A$20,$A5,$B$9:$B$20,$B5).
Your answer of 165, and there it is highlighted in red.
We’ll copy that across and no need to worry about that third lookup because as we copied across, it automatically points to a new set of columns so here, we’re pulling data from Column D and data from Column E. The important that we didn’t have a $ there before that E, that way the column changes as we copied across.
All right, Mike, let’s see what you got.
Mike Girvin: Thanks, MrExcel.
Hey, =SUMIFS to do this Lookup when there’s no duplicates, I love it.
I also love it because that’s =SUMIFS will be a little bit faster calculating than the Lookup method I’m going to use here.
Hey, I’m going to go straight for the D functions, and I’m going to use the =DGET( function.
That can look something up.
Hey, we have a proper data set right here with field names at the top so we’ll give it the whole data set all the way down, F4 to lock it, comma, the Field that means within this table.
Which Field do we want to get the thing from?
Relative cell reference, comma, and then the criteria because we have Field name in our criteria below; we’re allowed to use this function.
That means it has to find this and that in the same row so I highlight the field names and the criteria F4, close parenthesis that will work =DGET($A$8:$H$20,C4,SA$A:$B$5).
Control Enter and copy it and drag it over.
I go to the last cell F2.
It’s looking good.
Now, if I go and change this to Property1, oh, I get a #VALUE! there because I have to go ahead and change both of them so I’m going to go ahead and add in =IFERROR(DGET($A$8:$H$20,C4,$A$A:$B$5),””).
Ctrl Enter, copy it over.
Now, let’s go ahead and try that again.
Property2 and I’m going to do Property2 section11 and there we go.
Now, I like the =SUMIFS better because the =DGET will calculate a little bit faster.
All right, throw back to MrExcel.
Bill Jelen: Hey, wow, Mike.
I figured you’re going to do a crazy array formula there but =DGET, how old school is that?
=DGET certainly works in this case if mitrammm really just has one set of things for return, but I was afraid that they were going to copy that down and =DGET unless we’re using a data table is not going to work.
Now, let’s see, you said that it will be faster to use =SUMIFS instead of =DGET, let’s see what happens.
Charles Williams: Charles Williams here.
Now, let’s see how those solutions stack up using FastExcel.
Bill Jelen: All right, so here we are on the FastExcel V3.
I went into the menu Calculation Mode and we’re going to try this =SUMIFS or recount the range – 0.182; 0.182 Milliseconds.
We’ll try it a couple of times, 0.225, and Calc Range 0.199.
So, right around 0.2 Milliseconds and then we’ll try the =DGET and see how much slower that is than this =SUMIFS.
Calc Range – ha, hey, 0.358.
So, almost twice as fast or twice as long to calculate – 0.35.
But, really, in this case if there’s just six of them, 0.2 Milliseconds versus 0.345 Milliseconds really, it’s a virtual time although if we had thousands of them, that will be wild.
Michael, I love the =DGET.
It’s great to be back doing Dueling Excel Podcast again.
Thank you everyone for stopping by, we’ll see you next week for Dueling Excel Podcast from MrExcel and Excel is fun.
