Alicha asks how can you return many columns from a table using VLOOKUP? I have a couple of tips to make this process easier. Episode 613 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Alisha.
If you have a question for the netcast, please feel free to either drop me an email or leave me a voicemail and we'll get to you on a future podcast.
Now Alicia's question is about using VLOOKUP.
She said she understands how to do a VLOOKUP to get one column, but what happens when you need to copy that over to get many columns?
And I have a couple of tricks to make this a little bit easier.
"VLOOKUP" stands for vertical lookup, and we say hey we want to go find this particular account number within this table, and the left most column of the table has to have the account number in that column, and then any other information you want to look up to the right of the column.
Now hit the F4 key to make that be absolute and we'll ask for the second column and finally we have to put the word FALSE, because we're looking for an exact match not a close match.
And you'll see that we get the answer in the second column basically in our vlookup.
I'll copy that down to all of the records, and it will work perfectly.
But if I need to copy this to the right, a couple of things we would have to do.
First of all when we were specifying that we want to LOOKUP A2, we need to put a $ before the A because as we copy this to the right we always want to make sure that, we're pointing at column A to get the lookup value.
So now when we copy it to the right We have the small problem that unfortunately we have to go in and edit the 2 and change it to a 3, to a 4, to a 5 and so on.
Now I have a great workaround for this it seems a little bit convoluted, but it's much easier than having to go through and edit all of your columns.
Back here in the original formula when we asked for the number 2, we want the second column in our lookup table.
Rather than just type the number 2 in there, which is actually hard-coded, So then it becomes 2 as we copy it across.
If I ask for the column of B1, column of B1, that's going to be a very convoluted way to return to 2.
B is the second column so asking for the column of B1 will return to 2.
But the advantage of that method is, as I copy to the right it will automatically change to the column of C1, the column of D1 and the column of E1.
Which will give me the second third fourth and fifth columns from the lookup table.
Now when I copy this new formula down to all of the rows in the data set, it automatically copies.
So there it is, a VLOOKUP by putting a $ before the column letter of the lookup value and then also instead of hard-coding the column number using the column function column of B1 and the reason I use B1 is because B happens to be the second column.
Even if this vlookup was out in column Z, I would still be asking for the column at B1 because that represents the second column in the table.
So there you have it. Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Alisha.
If you have a question for the netcast, please feel free to either drop me an email or leave me a voicemail and we'll get to you on a future podcast.
Now Alicia's question is about using VLOOKUP.
She said she understands how to do a VLOOKUP to get one column, but what happens when you need to copy that over to get many columns?
And I have a couple of tricks to make this a little bit easier.
"VLOOKUP" stands for vertical lookup, and we say hey we want to go find this particular account number within this table, and the left most column of the table has to have the account number in that column, and then any other information you want to look up to the right of the column.
Now hit the F4 key to make that be absolute and we'll ask for the second column and finally we have to put the word FALSE, because we're looking for an exact match not a close match.
And you'll see that we get the answer in the second column basically in our vlookup.
I'll copy that down to all of the records, and it will work perfectly.
But if I need to copy this to the right, a couple of things we would have to do.
First of all when we were specifying that we want to LOOKUP A2, we need to put a $ before the A because as we copy this to the right we always want to make sure that, we're pointing at column A to get the lookup value.
So now when we copy it to the right We have the small problem that unfortunately we have to go in and edit the 2 and change it to a 3, to a 4, to a 5 and so on.
Now I have a great workaround for this it seems a little bit convoluted, but it's much easier than having to go through and edit all of your columns.
Back here in the original formula when we asked for the number 2, we want the second column in our lookup table.
Rather than just type the number 2 in there, which is actually hard-coded, So then it becomes 2 as we copy it across.
If I ask for the column of B1, column of B1, that's going to be a very convoluted way to return to 2.
B is the second column so asking for the column of B1 will return to 2.
But the advantage of that method is, as I copy to the right it will automatically change to the column of C1, the column of D1 and the column of E1.
Which will give me the second third fourth and fifth columns from the lookup table.
Now when I copy this new formula down to all of the rows in the data set, it automatically copies.
So there it is, a VLOOKUP by putting a $ before the column letter of the lookup value and then also instead of hard-coding the column number using the column function column of B1 and the reason I use B1 is because B happens to be the second column.
Even if this vlookup was out in column Z, I would still be asking for the column at B1 because that represents the second column in the table.
So there you have it. Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
