How can you do a VLOOKUP from one worksheet to another worksheet? It is simple to do, but hard to remember the syntax. In Episode 672, I'll show you the easy way to enter these formulas.
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.
Today I have a question that was sent in.
The person said how can we do a VLOOKUP from one worksheet to another worksheet.
Well, you know it's easy to do this, but it's really hard to remember the syntax of how you point to that other sheet.
So I never actually type the formula out. I always just point to the other area.
So for example, let's say we needed to look up this cost here C101.
I'm going to hit the F4 key 3 times to lock to make sure that I'm always looking at column A, put in the comma.
Now this is the point where I need to go look at that other lookup table Which may be on another sheet, so I'm actually going to click down here in the tab "Last year" I'm going to highlight my entire range.
And ofcourse I want that to be absolute, so I'll hit the F4 key once and which column do I want the second column and FALSE.
All right now you'll see up here, in the formula bar that they've actually built the syntax for us.
It turns out that we have to put an apostrophe, the sheet name, another apostrophe and then an exclamation point before the address.
Click "Enter" We'll be able to copy that down and it'll work everywhere.
Now there's a great shortcut depending on how you name your sheets.
In this case the other worksheet was called Last Year.
It has a space between Last and Year and when you do that, you have to remember to put the apostrophes in.
If you would instead rename the sheet I'll just call it LastYear without a space, and we'll go back and look at that original formula.
You see the formula now is actually much simpler.
There's no apostrophes at all.
You can just put the sheet name, the !
and then the address.
Everything works great.
So it's very easy to set up links from one worksheet to another.
I can never remember whether the apostrophes and the exclamation points come inside of each other.
So I always just simply point to the area using the mouse.
And you're good to go.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today I have a question that was sent in.
The person said how can we do a VLOOKUP from one worksheet to another worksheet.
Well, you know it's easy to do this, but it's really hard to remember the syntax of how you point to that other sheet.
So I never actually type the formula out. I always just point to the other area.
So for example, let's say we needed to look up this cost here C101.
I'm going to hit the F4 key 3 times to lock to make sure that I'm always looking at column A, put in the comma.
Now this is the point where I need to go look at that other lookup table Which may be on another sheet, so I'm actually going to click down here in the tab "Last year" I'm going to highlight my entire range.
And ofcourse I want that to be absolute, so I'll hit the F4 key once and which column do I want the second column and FALSE.
All right now you'll see up here, in the formula bar that they've actually built the syntax for us.
It turns out that we have to put an apostrophe, the sheet name, another apostrophe and then an exclamation point before the address.
Click "Enter" We'll be able to copy that down and it'll work everywhere.
Now there's a great shortcut depending on how you name your sheets.
In this case the other worksheet was called Last Year.
It has a space between Last and Year and when you do that, you have to remember to put the apostrophes in.
If you would instead rename the sheet I'll just call it LastYear without a space, and we'll go back and look at that original formula.
You see the formula now is actually much simpler.
There's no apostrophes at all.
You can just put the sheet name, the !
and then the address.
Everything works great.
So it's very easy to set up links from one worksheet to another.
I can never remember whether the apostrophes and the exclamation points come inside of each other.
So I always just simply point to the area using the mouse.
And you're good to go.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
