It is a game of Dueling Excel as Bill Jelen and Mike Gel Girvin bring you their own ways for doing two-way VLOOKUPS. Episode 965 shows you how.
This video is the 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 video is the 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:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, Alright! Welcome back to the Mr. Excel netcast.
This is an exciting day because I'm here in Seattle.
In the office of Mike Gel Girvin.
Now, Mike and I are probably the most prolific Excel video guys out there.
How many videos give you have?
600 on YouTube or something.
Yeah!
Yeah, I have...
This is what coming up on 850 or something like that.
So, the question we're gonna work on today.
We're going to do two different ways, My way, Mike's way.
It is how to do a two way LOOKUP do we LOOKUP?
So, we have some nice validation set up here and Mike show you how you set this up.
We want to choose the the car and the month and have it figure out for us the intersection of those two.
Now, I'm going to do this.
What I think is, I don't know.
The way that I would always do it and that's doing two matches and then in index.
I'm going to build it in three pieces.
So, equal match we gonna look for that car name within this range over here.
I'll press F4 on and i don't know why.
Just because I always press F4 and then zero and that's going to tell me where Honda is.
So, Honda is in row 3 and you see that if I choose a different cell for example Subaru, then it shows up in row 5.
Okay! Now I'm going to do a second Match in.
The thing that's amazing to me about MATCH, you know we have VLOOKUP, and we have HLOOKUP.
But MATCH goes both ways.
It goes vertically or it goes horizontally.
So I say, Hey, I want to find this month, in the range over here .
Press F4 again and zero again.
And it tells me that we are in row or column two.
Now to make this all work, going to use the INDEX function.
Of course when I first saw INDEX, wow! that's a silly function.
I never had a chance to use that.
But what it does.
Says we have a rectangular range.
We need a certain row, and then a certain call.
And so we get 3033.
Let's see if it worked.
I always hold my breath at this one.
Of course it's hard to talk that 3033 is working.
Let's try something else.
Will choose March and then Saab and we get 5480 Sweet Okay!
Now, once I have that formula working, I then go back and I want to put it all together into one cell.
That way someone will think, hey this guy's really MrExcel.
So, I'm going to copy right there from the formula.
That's A14.
So, here where I have A14, I'll now [ CTRL+V ] to paste Good half way there, and then we come back to the second formula.
Build this whole then [ CTRL+C ], and then come here and paste over A15.
Good now the formula is working I'm going to copy the whole thing [ CTRL+C ].
Put up your where Mike that you go up in the yellow cell and erase this evidence.
I had to build it in three pieces.
That way is all impressive.
Anyone who comes along being like wow!
That's the most amazing format I've ever seen but I have a sneaking suspicion that Mike is going to show me up here.
He's gonna have any cooler way to do it.
So we'll turn this over Mike.
Thanks MrExcel.
I don't think I'm going to show you up.
I'm going to show you probably a more convoluted difficult way, but it's pretty cool.
So we'll check it out here.
We're actually going to use names, the Intersecter Operator and the INDIRECT function.
Now, we have April and Chevy.
I'd like to name this column January, this column February and all of the corresponding rows the name in the row header.
The quick way to do that is to use name from selection.
create name from selection.
I'm going to highlight this whole range and I'm going to use the keyboard shortcut [ CTRL+SHIFT+F3 ] And it's asking hey, I got the top row, which is right here, and I have the left column.
Click ok.
Now, I'd never believed that when I do that right off the bat.
So, I'm actually going to [ CTRL+F3 ] To see if it got all the names.
Sure enough it does in 2003.
That's defined names here.
It's named manager.
All right? I am going to close.
Now, I'd like to add data validation here and here, and then we'll build our formula here.
Based on those names and indirect the keyboard shortcut from 2003, that still works.
In this version, it's [ ALT+D+L ] and I'm going to allow a list.
And the source for this one, this is the car.
So, I'm going to highlight this range here.
Click OK and then here [ ALT+D+L ] Tab L Tab.
And then I need the months Click OK.
Now, let's go ahead and build our formula.
Actually, before we do the indirect, let's see how the Intersect Operator works.
Equals and then I'm going to highlight the April range.
Notice it shows up as a name because it knows its name.
Space...
Space is the Intersect Operator, and then I got Chevy.
So, it should because the two values are intersecting right there.
And there's a space that should give us 4511.
Now, wouldn't it be nice instead of having to do this formula and highlight it, we could just do a formula like this, equals Chevy space April.
Hey, it's got the interceptor operator.
Note those are names right!
Enter And all set.
That means there's no intersection.
Hey, all we have to do, sense this if I highlight it and hit the F9 key.
That's evaluate.
That actually shows me that that's a word.
Those quotes mean text.
I'm going to [ CTRL+Z ].
So what we need to do is, use the INDIRECT function.
It converts text to a reference.
So I'm going to type INDIRECT.
I'm a bad typer.
Actually you should have been here watching MrExcel use the keyboard.
I couldn't believe it on such a bad typer, but I have to look down here Alright! let's see if this works Notice that's a pretty weird formula because there's a space, but when I hit Enter it seems to work.
Let's check it Saab right!
and March and sure enough.
Let's see did it get it whereas March.
There it is it seemed to get it.
We so uh!
We'll see a next trick.
and MrExcel Excel is going to say a bye here also.
Alright! Well hey, thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, Alright! Welcome back to the Mr. Excel netcast.
This is an exciting day because I'm here in Seattle.
In the office of Mike Gel Girvin.
Now, Mike and I are probably the most prolific Excel video guys out there.
How many videos give you have?
600 on YouTube or something.
Yeah!
Yeah, I have...
This is what coming up on 850 or something like that.
So, the question we're gonna work on today.
We're going to do two different ways, My way, Mike's way.
It is how to do a two way LOOKUP do we LOOKUP?
So, we have some nice validation set up here and Mike show you how you set this up.
We want to choose the the car and the month and have it figure out for us the intersection of those two.
Now, I'm going to do this.
What I think is, I don't know.
The way that I would always do it and that's doing two matches and then in index.
I'm going to build it in three pieces.
So, equal match we gonna look for that car name within this range over here.
I'll press F4 on and i don't know why.
Just because I always press F4 and then zero and that's going to tell me where Honda is.
So, Honda is in row 3 and you see that if I choose a different cell for example Subaru, then it shows up in row 5.
Okay! Now I'm going to do a second Match in.
The thing that's amazing to me about MATCH, you know we have VLOOKUP, and we have HLOOKUP.
But MATCH goes both ways.
It goes vertically or it goes horizontally.
So I say, Hey, I want to find this month, in the range over here .
Press F4 again and zero again.
And it tells me that we are in row or column two.
Now to make this all work, going to use the INDEX function.
Of course when I first saw INDEX, wow! that's a silly function.
I never had a chance to use that.
But what it does.
Says we have a rectangular range.
We need a certain row, and then a certain call.
And so we get 3033.
Let's see if it worked.
I always hold my breath at this one.
Of course it's hard to talk that 3033 is working.
Let's try something else.
Will choose March and then Saab and we get 5480 Sweet Okay!
Now, once I have that formula working, I then go back and I want to put it all together into one cell.
That way someone will think, hey this guy's really MrExcel.
So, I'm going to copy right there from the formula.
That's A14.
So, here where I have A14, I'll now [ CTRL+V ] to paste Good half way there, and then we come back to the second formula.
Build this whole then [ CTRL+C ], and then come here and paste over A15.
Good now the formula is working I'm going to copy the whole thing [ CTRL+C ].
Put up your where Mike that you go up in the yellow cell and erase this evidence.
I had to build it in three pieces.
That way is all impressive.
Anyone who comes along being like wow!
That's the most amazing format I've ever seen but I have a sneaking suspicion that Mike is going to show me up here.
He's gonna have any cooler way to do it.
So we'll turn this over Mike.
Thanks MrExcel.
I don't think I'm going to show you up.
I'm going to show you probably a more convoluted difficult way, but it's pretty cool.
So we'll check it out here.
We're actually going to use names, the Intersecter Operator and the INDIRECT function.
Now, we have April and Chevy.
I'd like to name this column January, this column February and all of the corresponding rows the name in the row header.
The quick way to do that is to use name from selection.
create name from selection.
I'm going to highlight this whole range and I'm going to use the keyboard shortcut [ CTRL+SHIFT+F3 ] And it's asking hey, I got the top row, which is right here, and I have the left column.
Click ok.
Now, I'd never believed that when I do that right off the bat.
So, I'm actually going to [ CTRL+F3 ] To see if it got all the names.
Sure enough it does in 2003.
That's defined names here.
It's named manager.
All right? I am going to close.
Now, I'd like to add data validation here and here, and then we'll build our formula here.
Based on those names and indirect the keyboard shortcut from 2003, that still works.
In this version, it's [ ALT+D+L ] and I'm going to allow a list.
And the source for this one, this is the car.
So, I'm going to highlight this range here.
Click OK and then here [ ALT+D+L ] Tab L Tab.
And then I need the months Click OK.
Now, let's go ahead and build our formula.
Actually, before we do the indirect, let's see how the Intersect Operator works.
Equals and then I'm going to highlight the April range.
Notice it shows up as a name because it knows its name.
Space...
Space is the Intersect Operator, and then I got Chevy.
So, it should because the two values are intersecting right there.
And there's a space that should give us 4511.
Now, wouldn't it be nice instead of having to do this formula and highlight it, we could just do a formula like this, equals Chevy space April.
Hey, it's got the interceptor operator.
Note those are names right!
Enter And all set.
That means there's no intersection.
Hey, all we have to do, sense this if I highlight it and hit the F9 key.
That's evaluate.
That actually shows me that that's a word.
Those quotes mean text.
I'm going to [ CTRL+Z ].
So what we need to do is, use the INDIRECT function.
It converts text to a reference.
So I'm going to type INDIRECT.
I'm a bad typer.
Actually you should have been here watching MrExcel use the keyboard.
I couldn't believe it on such a bad typer, but I have to look down here Alright! let's see if this works Notice that's a pretty weird formula because there's a space, but when I hit Enter it seems to work.
Let's check it Saab right!
and March and sure enough.
Let's see did it get it whereas March.
There it is it seemed to get it.
We so uh!
We'll see a next trick.
and MrExcel Excel is going to say a bye here also.
Alright! Well hey, thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
