In today's dueling Excel podcast, Mike and Bill compare formulas when you have to do a lookup that matches two values in two columns. ...This Podcast contains info for an upcoming Live" Dueling Webinar with Mike Girvin and Bill Jelen!!
Transcript of the video:
To enjoy the dueling podcast, I know, I do.
Mike and I are going to do a live. Friday October 29, 3 p.m.
Eastern Noon Pacific.
All you have to do is stop by, sign in.
You can watch us "tinyurl.com/xltv29" Free to join, we'll do a couple of dueling podcasts and see if you have any QA.
Stop by.
Deulling Excel podcast, episode 52.
Look up 2 Values.
Hey! Welcome back to the dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
Be joined by Mike Girvin, from Excel Is Fun.
Great questions send in from YouTube this week.
How do you do a look up when there are two look up by it?
So, they want to be able to choose a date here, and then also choose a person.
So, 4, 10 .4 October, 2010 and Tina.
We want to return the 275.
How do we get the 275?
You know in my initial reaction here, Mike was to use a concatenated key.
How will we take the date and the rep and join that together, over in column A and then when we do our VLOOKUP.
We could look up the concatenation of F2&G2.
and Just like a real VLOOKUP at that ponit.
But, you know I wasn't sure if we were allowed to add an extra column.
They didn't say that's the problem.
I started let's see we can solve this somehow without adding an extra column.
And so I'm going to use a trick that came along in Excel 2007 called SUMIF.
SUMIF, says hey!
We're going to go add up all of these values over here in Column C.
We'll press [ F4 ], and pairs of criteria.
Now, first pair of criteria is look through all of the dates over, in Column A and see if that's equal to cell E2.
Next pair of criteria look through all the dates in Column B, and See if that's equal to Tina Sales rep there, and we get our 275 So, there's the formula SUMIF.
We're adding up all of these values in C and checking to see, if the date and A matches E and if the name and B matches F.
Now, let's just try it here will change from Tina to Sue, see we get the 373.
So, that's working alright.
So, now you know, usually VLOOKUP will return only the first value and I guess the one problem with this may be a problem, may not be a problem.
Is that, if Tina was in there twice for October 4th, it's going to add up those values.
Might be what they need to do.
Alright Mike! Let's see what you have.
Thanks, MrExcel.
Hey. I love that SUMIFs.
Because there are other functions like, SUM, Product D, SUM D, Get. That we could use to match two Criteria and get a value.
But, SUMIF is the fastest calculating amoungst all of those.
Now, this does require you don't have any duplets and we don't here.
Hey yeah! So SUMIF is great, but if you don't 2007 and 2010, how about the D GET function?
D functions are database functions, D GET is just one of them, there's D SUM, D GET, and a few others.
It means please go get a value.
So, database...
Your database has to, have field names at the top to identify the columns.
Comma (,) and then the field you either have to type it in or click on it.
That's the field with the value you want to return and Criteria.
You got to actually put the field names typed into cells and the two Criteria, below.
This means and both of these this and this has to match.
And this sometimes is a drawback because not all the templates, that you build. You want to actually type out all the criteria with field names.
But, nevertheless for this example, that will do it if I do this drop down right here.
We can get the same value.
So, if you're in earlier versions, and you can't do SUMIFs then use this.
Now, what if this...
Let's look at this example back here that MrExcel did, this is awesome!
If these values were not numbers, but they were text absolutely beautiful, you add an extra column, and you can see right here when you join.
I'm going to hit the [ F9 ] key.
You can see it actually creates a single value from two.
So, you take two Criteria and make it one.
[ Escape ] and then this VLOOKUP, totally beautiful.
Lots of people know VLOOKUP.
This is a great solution.
Now, let's just look at a hypothesis.
What if, you had words here?
You are not allowed to add an extra column.
What in the old, what you do?
Then let's take a look here's a little data set and we want to return the project.
Well, we can use the LOOKUP function, INDEX An INDEX is great.
You can just say, hey! These are the items, I want to return Comma (,) and now all we need is the row number.
Right, so for Sue... That's...
This one. That's like the 9th value in this data set.
So, what do we do?
Well, We can use the MATCH function.
M A T C H.
Can take Criteria and actually tell you the row number.
So, lookup value, just like MrExcel's VLOOKUP.
We join and just like his extra column.
You join the values right directly in the formula, comma and the difference here is, we're not going to add the extra Column.
We're going to in this case, create that extra Column by joining this column and then [ shift ] + [ 7 ], to get the '&' this column.
Now, if you highlight this and hit the [ F9 ] key, you can see sure enough it creates a single column with our values.
Now, when you put... When you do '&' on two columns like this.
You actually create an array.
So, we're going to have to use [ cTRL+ shift+ enter ] for this formula.
Finally, we have to put 0 for match and that's so the MATCH function right here.
If I don't like this and hit the [ F9 ], it delivers the row number.
[ ctrl + z ], and then I close parentheses on the INDEX and because we created an array, you have to hold [ ctrl+shift+ enter ].
So, you actually have to [ enter ] that, with the key strokes [ ctrl+ shift +enter ] and that curly bracket (), means Excel understood that it's an array for me and now if I change this.
Looks like it's working.
Now one final note here.
If I copy this and I'm going to paste it down here.
Right, if you didn't like doing [ ctrl+ shift +enter ], there's one further trick, we could do.
We could take this part of the formula which is the part that's requiring that we [ ctrl +shift+enter ], and if we put it inside the index function.
It'll actually work without [ ctrl +shift+enter ].
I'm going to [ ctrl + x ] and then I'm going to type.
Now, look at where we are, we're right here.
I'm going to type index again.
Now, two things are awesome about the index.
If you look at this screen tip it says array, that means it can handle an array.
If you look up and help it also will tell you.
So, I can plop that right there, and it will work now.
But, we need the whole column right! Comma (,) row number.
Usually, put 3, 4, whatever.
But, if you'll leave it blank, it delivers all the rows.
So, I'm simply going to close parenthesis.
Now, look at this all we did is put that...
and joined two Columns inside the index with an extra little Comma and when I hit [ F9 ].
No way! It creates that same Column, [ ctrl + z ].
But, we don't have to [ ctrl +shift+enter ] So, hit [ enter ].
Just [ enter ].
There's no curly brackets there, that won't work.
Alright! For our particular application though sorry, SUMIF wins.
It's a built-in function, it calculates fastest.
Alright! I'm going to throw it back to MrExcel.
MrExcel: That's beautiful.
Mike you hadn't been reminiscing with DGET DSUM used to be my favorite function in the whole world back in the 90s, before pivot Tables came along D GET.
Boy! That's a blast from the past and then the index with the special array format.
I've always seen that in the help never thought to use.
Actually, I think he used it, dueling once before.
I'm good to see it again.
So, hey! And again.
If you enjoy these dueling excel podcast check out on, Friday, October 29th 2010.
I'm gonna be doing this live, on a free webinar.
Just go to, "tinyurl.com/xltv29." You'll be able to hear us, through your computer speakers.
Make sure you have some speakers, maybe some headphones.
Your work of course which will be alright!
Thanks for stopping by.
We'll see you next time for another dueling Excel podcast, from MrExcel and Excel Is Fun.
Mike and I are going to do a live. Friday October 29, 3 p.m.
Eastern Noon Pacific.
All you have to do is stop by, sign in.
You can watch us "tinyurl.com/xltv29" Free to join, we'll do a couple of dueling podcasts and see if you have any QA.
Stop by.
Deulling Excel podcast, episode 52.
Look up 2 Values.
Hey! Welcome back to the dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
Be joined by Mike Girvin, from Excel Is Fun.
Great questions send in from YouTube this week.
How do you do a look up when there are two look up by it?
So, they want to be able to choose a date here, and then also choose a person.
So, 4, 10 .4 October, 2010 and Tina.
We want to return the 275.
How do we get the 275?
You know in my initial reaction here, Mike was to use a concatenated key.
How will we take the date and the rep and join that together, over in column A and then when we do our VLOOKUP.
We could look up the concatenation of F2&G2.
and Just like a real VLOOKUP at that ponit.
But, you know I wasn't sure if we were allowed to add an extra column.
They didn't say that's the problem.
I started let's see we can solve this somehow without adding an extra column.
And so I'm going to use a trick that came along in Excel 2007 called SUMIF.
SUMIF, says hey!
We're going to go add up all of these values over here in Column C.
We'll press [ F4 ], and pairs of criteria.
Now, first pair of criteria is look through all of the dates over, in Column A and see if that's equal to cell E2.
Next pair of criteria look through all the dates in Column B, and See if that's equal to Tina Sales rep there, and we get our 275 So, there's the formula SUMIF.
We're adding up all of these values in C and checking to see, if the date and A matches E and if the name and B matches F.
Now, let's just try it here will change from Tina to Sue, see we get the 373.
So, that's working alright.
So, now you know, usually VLOOKUP will return only the first value and I guess the one problem with this may be a problem, may not be a problem.
Is that, if Tina was in there twice for October 4th, it's going to add up those values.
Might be what they need to do.
Alright Mike! Let's see what you have.
Thanks, MrExcel.
Hey. I love that SUMIFs.
Because there are other functions like, SUM, Product D, SUM D, Get. That we could use to match two Criteria and get a value.
But, SUMIF is the fastest calculating amoungst all of those.
Now, this does require you don't have any duplets and we don't here.
Hey yeah! So SUMIF is great, but if you don't 2007 and 2010, how about the D GET function?
D functions are database functions, D GET is just one of them, there's D SUM, D GET, and a few others.
It means please go get a value.
So, database...
Your database has to, have field names at the top to identify the columns.
Comma (,) and then the field you either have to type it in or click on it.
That's the field with the value you want to return and Criteria.
You got to actually put the field names typed into cells and the two Criteria, below.
This means and both of these this and this has to match.
And this sometimes is a drawback because not all the templates, that you build. You want to actually type out all the criteria with field names.
But, nevertheless for this example, that will do it if I do this drop down right here.
We can get the same value.
So, if you're in earlier versions, and you can't do SUMIFs then use this.
Now, what if this...
Let's look at this example back here that MrExcel did, this is awesome!
If these values were not numbers, but they were text absolutely beautiful, you add an extra column, and you can see right here when you join.
I'm going to hit the [ F9 ] key.
You can see it actually creates a single value from two.
So, you take two Criteria and make it one.
[ Escape ] and then this VLOOKUP, totally beautiful.
Lots of people know VLOOKUP.
This is a great solution.
Now, let's just look at a hypothesis.
What if, you had words here?
You are not allowed to add an extra column.
What in the old, what you do?
Then let's take a look here's a little data set and we want to return the project.
Well, we can use the LOOKUP function, INDEX An INDEX is great.
You can just say, hey! These are the items, I want to return Comma (,) and now all we need is the row number.
Right, so for Sue... That's...
This one. That's like the 9th value in this data set.
So, what do we do?
Well, We can use the MATCH function.
M A T C H.
Can take Criteria and actually tell you the row number.
So, lookup value, just like MrExcel's VLOOKUP.
We join and just like his extra column.
You join the values right directly in the formula, comma and the difference here is, we're not going to add the extra Column.
We're going to in this case, create that extra Column by joining this column and then [ shift ] + [ 7 ], to get the '&' this column.
Now, if you highlight this and hit the [ F9 ] key, you can see sure enough it creates a single column with our values.
Now, when you put... When you do '&' on two columns like this.
You actually create an array.
So, we're going to have to use [ cTRL+ shift+ enter ] for this formula.
Finally, we have to put 0 for match and that's so the MATCH function right here.
If I don't like this and hit the [ F9 ], it delivers the row number.
[ ctrl + z ], and then I close parentheses on the INDEX and because we created an array, you have to hold [ ctrl+shift+ enter ].
So, you actually have to [ enter ] that, with the key strokes [ ctrl+ shift +enter ] and that curly bracket (), means Excel understood that it's an array for me and now if I change this.
Looks like it's working.
Now one final note here.
If I copy this and I'm going to paste it down here.
Right, if you didn't like doing [ ctrl+ shift +enter ], there's one further trick, we could do.
We could take this part of the formula which is the part that's requiring that we [ ctrl +shift+enter ], and if we put it inside the index function.
It'll actually work without [ ctrl +shift+enter ].
I'm going to [ ctrl + x ] and then I'm going to type.
Now, look at where we are, we're right here.
I'm going to type index again.
Now, two things are awesome about the index.
If you look at this screen tip it says array, that means it can handle an array.
If you look up and help it also will tell you.
So, I can plop that right there, and it will work now.
But, we need the whole column right! Comma (,) row number.
Usually, put 3, 4, whatever.
But, if you'll leave it blank, it delivers all the rows.
So, I'm simply going to close parenthesis.
Now, look at this all we did is put that...
and joined two Columns inside the index with an extra little Comma and when I hit [ F9 ].
No way! It creates that same Column, [ ctrl + z ].
But, we don't have to [ ctrl +shift+enter ] So, hit [ enter ].
Just [ enter ].
There's no curly brackets there, that won't work.
Alright! For our particular application though sorry, SUMIF wins.
It's a built-in function, it calculates fastest.
Alright! I'm going to throw it back to MrExcel.
MrExcel: That's beautiful.
Mike you hadn't been reminiscing with DGET DSUM used to be my favorite function in the whole world back in the 90s, before pivot Tables came along D GET.
Boy! That's a blast from the past and then the index with the special array format.
I've always seen that in the help never thought to use.
Actually, I think he used it, dueling once before.
I'm good to see it again.
So, hey! And again.
If you enjoy these dueling excel podcast check out on, Friday, October 29th 2010.
I'm gonna be doing this live, on a free webinar.
Just go to, "tinyurl.com/xltv29." You'll be able to hear us, through your computer speakers.
Make sure you have some speakers, maybe some headphones.
Your work of course which will be alright!
Thanks for stopping by.
We'll see you next time for another dueling Excel podcast, from MrExcel and Excel Is Fun.
