VLOOKUP week continues.... Today, you have to lookup an account number and return the results from four columns in the lookup table. Episode 1123 shows you four possible solutions.
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:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically we start out with massive amounts of data - how are we going to analyse this?
Well let's fire up a PivotTable and see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, VLOOKUP week continues.
We're talking about VLOOKUP week. This time I have to do a VLOOKUP here in column B, and I have to copy a VLOOKUP over to C, D and E to grab all 4 columns.
So first thing we want to do to do is Equal VLOOKUP and we point back to column A1, press that for 1,2,3 times to lock down just the column A.
That way, as we copy this across, it's always going to look back to column A and then over here we choose our table.
So Ctrl + Shift + Down Arrow, Shift + Right, Right, Right, Right.
Press F4 one single time to lock that table down and lock on to comma false.
Alright, so we copy that across and you see that is always going to be grabbing column 2.
This is the painful step we have to go through and edit these one at a time.
Not bad for 4 of them but worse if you have monthly data, or 36 columns or something like that.
Go through and edit those.
Alright, so some things that I have seen - I'm going onto the next worksheet.
Sometimes we enter an extra little row up here and we put the numbers 2,3,4,5 and we use =VLOOKUP.
Again, back to column A1,2,3 - F4.
Choose our range over here - Ctrl + Shift + Down Arrow.
Press F4 one single time and then for the column number, we point up here to B1 and we press F4 one, two times to freeze it down to row 1.
False, and now that allows us to copy straight across to get the different answers.
Double click the go handle.
Shoot it down.
Alright.
So that's another option.
The option which really, really, really is going to take a long time for excel to calculate is: Instead of using that B1, say we want the column of B1, column B1 is going to return the number 2, and the nice thing about that as we copy across.
That changes the column C1, D1 and E1 will give us 2,3,4 and 5, alright.
So that's a way to go.
The other way here, is just to abandon VLOOKUP completely, alright.
So I'm going to enter a new column here.
I'm going to call it "Where?", "Where?" the "Where?" column.
And here we're going to use the match function - Match.
Go find a 126 in the first column of this table press 4, and just comma Zero at the end of that - says we want an exact match.
Just like the false does and it says, hey, that's on row 26.
What good is that?
When we will we ever want to know what row something is on?
Ah, well very interesting.
We can then use a function called INDEX, =INDEX, = INDEX.
So when the index of this table over here in column J and very interesting.
I want to lock the rows down but not the column, because I want to be able to point to J,K,L and M and then as I copy across, the index of that, using this row number, the row number back here in column B and I'll press F4 one, two, three times to lock it down.
I don't need to specify the column number because there is only one column.
So that gives us our answer.
As I copy it across, it automatically points to K then L then M.
A great way to get all those answers in and this is fast - why?
Because Match only has to run once – Index runs lightening fast.
Then we can actually hide, from that column hide.
We really don't need these rows that I added up here.
A great, great way to go using Index and Match.
One Match, four Indexes! Definitely a faster way to go then having 4 VLOOKUPS.
Hey, alright, well I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically we start out with massive amounts of data - how are we going to analyse this?
Well let's fire up a PivotTable and see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, VLOOKUP week continues.
We're talking about VLOOKUP week. This time I have to do a VLOOKUP here in column B, and I have to copy a VLOOKUP over to C, D and E to grab all 4 columns.
So first thing we want to do to do is Equal VLOOKUP and we point back to column A1, press that for 1,2,3 times to lock down just the column A.
That way, as we copy this across, it's always going to look back to column A and then over here we choose our table.
So Ctrl + Shift + Down Arrow, Shift + Right, Right, Right, Right.
Press F4 one single time to lock that table down and lock on to comma false.
Alright, so we copy that across and you see that is always going to be grabbing column 2.
This is the painful step we have to go through and edit these one at a time.
Not bad for 4 of them but worse if you have monthly data, or 36 columns or something like that.
Go through and edit those.
Alright, so some things that I have seen - I'm going onto the next worksheet.
Sometimes we enter an extra little row up here and we put the numbers 2,3,4,5 and we use =VLOOKUP.
Again, back to column A1,2,3 - F4.
Choose our range over here - Ctrl + Shift + Down Arrow.
Press F4 one single time and then for the column number, we point up here to B1 and we press F4 one, two times to freeze it down to row 1.
False, and now that allows us to copy straight across to get the different answers.
Double click the go handle.
Shoot it down.
Alright.
So that's another option.
The option which really, really, really is going to take a long time for excel to calculate is: Instead of using that B1, say we want the column of B1, column B1 is going to return the number 2, and the nice thing about that as we copy across.
That changes the column C1, D1 and E1 will give us 2,3,4 and 5, alright.
So that's a way to go.
The other way here, is just to abandon VLOOKUP completely, alright.
So I'm going to enter a new column here.
I'm going to call it "Where?", "Where?" the "Where?" column.
And here we're going to use the match function - Match.
Go find a 126 in the first column of this table press 4, and just comma Zero at the end of that - says we want an exact match.
Just like the false does and it says, hey, that's on row 26.
What good is that?
When we will we ever want to know what row something is on?
Ah, well very interesting.
We can then use a function called INDEX, =INDEX, = INDEX.
So when the index of this table over here in column J and very interesting.
I want to lock the rows down but not the column, because I want to be able to point to J,K,L and M and then as I copy across, the index of that, using this row number, the row number back here in column B and I'll press F4 one, two, three times to lock it down.
I don't need to specify the column number because there is only one column.
So that gives us our answer.
As I copy it across, it automatically points to K then L then M.
A great way to get all those answers in and this is fast - why?
Because Match only has to run once – Index runs lightening fast.
Then we can actually hide, from that column hide.
We really don't need these rows that I added up here.
A great, great way to go using Index and Match.
One Match, four Indexes! Definitely a faster way to go then having 4 VLOOKUPS.
Hey, alright, well I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
