Mike & Bill take a look at various ways to do a 2-way lookup in this dueling Excel Episode 1124.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, we’ll be joined by Mike Girvin from ExcelIsFun!
Mike, all this week at MrExcel we've been doing VLOOKUP, we’ve talking about different VLOOKUP issues.
Now I want to do the 2-way lookup, so we have this table here with account numbers down the side, and months going across the top.
Based on these two cells, I want to find the value at the intersection.
Alright, so I'm going to build this in three pieces.
=MATCH, go find this value A114, I'll press F4, comma, in the left-hand column of this table Ctrl+Shift+Down arrow, press F4 again.
And then finally, we want an exact match, ,0 here is just like ,FALSE in the VLOOKUP, and says that that's on row 14.
Alright, now MATCH is really cool, because it can go both vertically or horizontally.
So for the column, go look for February within the first row of the table, we'll press F4 again, and again ,0 and that says “Hey, it's in column, 2.” Well, what good is that?
Well, now we're going to use this INDEX function, and the INDEX function is going to take our whole array, make sure to leave the headings out.
So we select B10:E233, press F4, and what we want, well we want this particular row and then this particular column, and there's our result, .352.
Let's just check it, A114, there it is, 0.352, and we'll enter something different here, like A104, .319, there it is, it's working.
OK, now that I have it working, check this out, this is my cool, cool trick.
See we refer to cell B5, here I'm going to go back to B5.
I'm going to go up to the formula bar and copy everything except for the =, Ctrl+C to copy, come back here, click on B5 and Ctrl+V to paste, cool.
Alright, next up, B6, come back to B6, I click on everything except for the =, Ctrl+C to copy, come back down here, B6, then paste, isn't that cool?
Ctrl+V Enter, and now I have my formula.
I can Ctrl+X, bring it back up here to the answer, and I no longer need these rows here, we can just Edit, Delete entire row, they're gone, and everything's working.
INDEX with a couple of MATCHes!
Alright, now, let's throw it over to Mike, see what Mike has!
Mike: Thanks MrExcel!
Whoa, Deja-vu!
You know, this is our 32nd duel, and all the way back, 32 weeks ago, duel number one, we did this 2-way look up!
Now, INDEX MATCH MATCH, we did that one last time, and we did an intersector operator.
Now, I'm going to show you a different method.
Now I want to compare it to INDEX MATCH MATCH, notice INDEX has the guts of the table, and then INDEX just needs a row number and a column number.
But what about VLOOKUP?
We could use VLOOKUP, because VLOOKUP always looks in the first column, and then we tell it- What?
I'm sorry, the first column and finds a row, jumps over, we tell it what column, and then it selects that value.
Well, instead of typing a column reference in, we can use the MATCH inside the VLOOKUP.
So let's see how that works, =VLOOKUP, I'm going to say the lookup value, since we want to find an accounting in the first column.
That's the way VLOOKUP works is, I'm going to select that, comma, the table is going to be not including the headers up here.
That range, Ctrl+Shift+Down arrow, that's the table array, comma, and then the column index.
Now 1-2-3-4, we put a 4 there for March, but knowing we can't hard code it in because we want it volatile, so we use the MATCH.
I say “Hey, look up that!” Comma, within this range right here, comma, and then exact match, and the MATCH will deliver the ordinal position, 1-2-3-4! MATCH inside the column index argument inside a VLOOKUP.
We don't need the last argument because this is sorted ascending, so we just close parentheses, and then Ctrl+Enter, so that will work right there, VLOOKUP and MATCH, if I type, say January.
Now, 32 weeks ago, what I did was the intersector operator, and I'm going to remind you of that here, because it's a, I don't think It says efficient as these two methods, but it's interesting.
This is a good one you drop on your friend that thinks they know everything about Excel, you know, “What about the intersector operator?” Now intersector operator works like this, we highlight some range, type a space, that’s the intersector operator.
And if we highlight another range that intersects, then it will always return the intersection.
Now, you don't want to have to do that each time, we want it linked to these up here.
Now, how do you name?
Because that means we'd have to name this whole column January, this one whole February, this accounting, this one accounting103, etc.
Well, there's a keyboard shortcut that does this automatically!
I'm going to select the whole table, this is the selection keyboard trick, Ctrl+*, and then the keyboard shortcut to name from selection is Ctrl+Shift+F3.
It’s going to say “Hey, are our names in the top row and the left column?” Yes, they are.
Now, let's go ahead and try it, =Jan Acct104, sure enough, it'll return that .357.
Now we don't want to have to type it in each time, so we need a formula that's actually linked to these cells.
The problem with this is that Excel sees text!
That’s text, “Jan” is considered text, so that's not going to work, there's no intersection.
So instead of using the raw cell reference, you have to use the INDIRECT function, which takes text like “Jan” and converts it to its reference.
That is a name in memory, so it does convert to a reference, space, and then INDIRECT again, and we click right there.
Ah, so that's a very obscure way to do a 2-way lookup, but sure enough, it'll work, if I type in March here, then they all update.
Alright, we'll see you next trick!
Bill: Oh my gosh Mike, you've got to be kidding me!
You can type in =Jan Acct101, I always lay it up with that in the SUM function, that’s incredible!
Mike, I couldn't believe it worked, I had to come here and test it out for myself!
There you go, you just dropped one on me.
Alright Mike, thanks for that great tip, and thanks to everyone else for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, we’ll be joined by Mike Girvin from ExcelIsFun!
Mike, all this week at MrExcel we've been doing VLOOKUP, we’ve talking about different VLOOKUP issues.
Now I want to do the 2-way lookup, so we have this table here with account numbers down the side, and months going across the top.
Based on these two cells, I want to find the value at the intersection.
Alright, so I'm going to build this in three pieces.
=MATCH, go find this value A114, I'll press F4, comma, in the left-hand column of this table Ctrl+Shift+Down arrow, press F4 again.
And then finally, we want an exact match, ,0 here is just like ,FALSE in the VLOOKUP, and says that that's on row 14.
Alright, now MATCH is really cool, because it can go both vertically or horizontally.
So for the column, go look for February within the first row of the table, we'll press F4 again, and again ,0 and that says “Hey, it's in column, 2.” Well, what good is that?
Well, now we're going to use this INDEX function, and the INDEX function is going to take our whole array, make sure to leave the headings out.
So we select B10:E233, press F4, and what we want, well we want this particular row and then this particular column, and there's our result, .352.
Let's just check it, A114, there it is, 0.352, and we'll enter something different here, like A104, .319, there it is, it's working.
OK, now that I have it working, check this out, this is my cool, cool trick.
See we refer to cell B5, here I'm going to go back to B5.
I'm going to go up to the formula bar and copy everything except for the =, Ctrl+C to copy, come back here, click on B5 and Ctrl+V to paste, cool.
Alright, next up, B6, come back to B6, I click on everything except for the =, Ctrl+C to copy, come back down here, B6, then paste, isn't that cool?
Ctrl+V Enter, and now I have my formula.
I can Ctrl+X, bring it back up here to the answer, and I no longer need these rows here, we can just Edit, Delete entire row, they're gone, and everything's working.
INDEX with a couple of MATCHes!
Alright, now, let's throw it over to Mike, see what Mike has!
Mike: Thanks MrExcel!
Whoa, Deja-vu!
You know, this is our 32nd duel, and all the way back, 32 weeks ago, duel number one, we did this 2-way look up!
Now, INDEX MATCH MATCH, we did that one last time, and we did an intersector operator.
Now, I'm going to show you a different method.
Now I want to compare it to INDEX MATCH MATCH, notice INDEX has the guts of the table, and then INDEX just needs a row number and a column number.
But what about VLOOKUP?
We could use VLOOKUP, because VLOOKUP always looks in the first column, and then we tell it- What?
I'm sorry, the first column and finds a row, jumps over, we tell it what column, and then it selects that value.
Well, instead of typing a column reference in, we can use the MATCH inside the VLOOKUP.
So let's see how that works, =VLOOKUP, I'm going to say the lookup value, since we want to find an accounting in the first column.
That's the way VLOOKUP works is, I'm going to select that, comma, the table is going to be not including the headers up here.
That range, Ctrl+Shift+Down arrow, that's the table array, comma, and then the column index.
Now 1-2-3-4, we put a 4 there for March, but knowing we can't hard code it in because we want it volatile, so we use the MATCH.
I say “Hey, look up that!” Comma, within this range right here, comma, and then exact match, and the MATCH will deliver the ordinal position, 1-2-3-4! MATCH inside the column index argument inside a VLOOKUP.
We don't need the last argument because this is sorted ascending, so we just close parentheses, and then Ctrl+Enter, so that will work right there, VLOOKUP and MATCH, if I type, say January.
Now, 32 weeks ago, what I did was the intersector operator, and I'm going to remind you of that here, because it's a, I don't think It says efficient as these two methods, but it's interesting.
This is a good one you drop on your friend that thinks they know everything about Excel, you know, “What about the intersector operator?” Now intersector operator works like this, we highlight some range, type a space, that’s the intersector operator.
And if we highlight another range that intersects, then it will always return the intersection.
Now, you don't want to have to do that each time, we want it linked to these up here.
Now, how do you name?
Because that means we'd have to name this whole column January, this one whole February, this accounting, this one accounting103, etc.
Well, there's a keyboard shortcut that does this automatically!
I'm going to select the whole table, this is the selection keyboard trick, Ctrl+*, and then the keyboard shortcut to name from selection is Ctrl+Shift+F3.
It’s going to say “Hey, are our names in the top row and the left column?” Yes, they are.
Now, let's go ahead and try it, =Jan Acct104, sure enough, it'll return that .357.
Now we don't want to have to type it in each time, so we need a formula that's actually linked to these cells.
The problem with this is that Excel sees text!
That’s text, “Jan” is considered text, so that's not going to work, there's no intersection.
So instead of using the raw cell reference, you have to use the INDIRECT function, which takes text like “Jan” and converts it to its reference.
That is a name in memory, so it does convert to a reference, space, and then INDIRECT again, and we click right there.
Ah, so that's a very obscure way to do a 2-way lookup, but sure enough, it'll work, if I type in March here, then they all update.
Alright, we'll see you next trick!
Bill: Oh my gosh Mike, you've got to be kidding me!
You can type in =Jan Acct101, I always lay it up with that in the SUM function, that’s incredible!
Mike, I couldn't believe it worked, I had to come here and test it out for myself!
There you go, you just dropped one on me.
Alright Mike, thanks for that great tip, and thanks to everyone else for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
