Ron wants to LOOKUP up Data from one workbook to use in a current workbook. Using HLOOKUP and Shortcuts to navigate between Workbooks, Bill shows us how to build a LOOKUP Formula that pulls the Data from the Table in the second workbook. Follow along with Episode #1650 to see how this is done.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1650: LOOKUP to Another Workbook.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question is sent in by Ron.
Ron wants to learn how to create LOOKUP that goes from one workbook, in this case, Book2.
I’m going to Ctrl + Tab over to the other workbook.
Here's our LOOKUP table.
So for whatever reason, the LOOKUP table is stored in another workbook.
We need to create, in this case, HLOOKUP between the two.
So =HLOOKUP( You know, I build the formula with the arrow keys so this point I press left, left, left to get back to the element I want to look up, comma =HLOOKUP(A2, All right now here's the point where we'd normally go point to the table and usually when I do this, the table is right out here, you know, to the right of my data but what we have to do is just use some navigation keys to get over to the other workbook.
So again, Ctrl + Tab will, while I'm working on this formula switch me to the other workbook.
Now that I'm in the other workbook I can use the arrow keys or the mouse.
I'm going to use Ctrl + Shift + down arrow, Ctrl + Shift + right arrow.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3 Okay now here's the one got you.
You’ll notice that they already put the dollar signs in.
I haven't pressed the F4 key and just out of force of habit I almost always at this point press the F4 key which doesn't lock down the columns, alright.
So whenever I'm building this, just because I'm not thinking about it, I'm just going really fast, I always find that I'm missing one of the sets of the dollar signs because I just instinctively press the F4 key.
So that's what you have to be kind of careful about.
All right, so we're done with that argument.
Press comma and then I can remember that it's the second row comma False because we're doing an exact match.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3,2,FALSE) All right and we're good to go.
We can copy that down.
Do you have to learn this syntax?
Heck no, you don't have to learn this syntax at all.
The apostrophe, open square bracket-- don't even worry about that.
You just have to go point to it.
Now one thing that's a little bit frustrating is that you have to-- once you switch over to that other workbook, they don't switch you back.
So if that's an issue for you, if the third or fourth or fifth arguments are something you want to point to here, you can always use Ctrl + Tab to get back but you could also do view-- either view side by side-- I always use ‘Arrange All’ and choose either horizontal or vertical.
I'll go with horizontal today.
Alright, so now that I can see both workbooks, I can do =HLOOKUP that cell comma and then just use the mouse down here.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3 I don't have to press F4 comma 2 =HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3,2 and then if I needed to point-- I mean it's silly in this case.
If I needed to point to some cell back up here, I could actually point to that cell because I can see both workbooks.
So the ‘Arrange All’ kind of-- will help you if you have to point to something in the other workbook and then come back and point to, in a later argument the original workbook.
You know, it certainly seems daunting if you have to know how to create this syntax, to type that syntax but no one really types that syntax.
I just point to it using the mouse or the arrow keys Ctrl + Tab here.
Easy.
Hey, I want to thank Ron for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1650: LOOKUP to Another Workbook.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question is sent in by Ron.
Ron wants to learn how to create LOOKUP that goes from one workbook, in this case, Book2.
I’m going to Ctrl + Tab over to the other workbook.
Here's our LOOKUP table.
So for whatever reason, the LOOKUP table is stored in another workbook.
We need to create, in this case, HLOOKUP between the two.
So =HLOOKUP( You know, I build the formula with the arrow keys so this point I press left, left, left to get back to the element I want to look up, comma =HLOOKUP(A2, All right now here's the point where we'd normally go point to the table and usually when I do this, the table is right out here, you know, to the right of my data but what we have to do is just use some navigation keys to get over to the other workbook.
So again, Ctrl + Tab will, while I'm working on this formula switch me to the other workbook.
Now that I'm in the other workbook I can use the arrow keys or the mouse.
I'm going to use Ctrl + Shift + down arrow, Ctrl + Shift + right arrow.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3 Okay now here's the one got you.
You’ll notice that they already put the dollar signs in.
I haven't pressed the F4 key and just out of force of habit I almost always at this point press the F4 key which doesn't lock down the columns, alright.
So whenever I'm building this, just because I'm not thinking about it, I'm just going really fast, I always find that I'm missing one of the sets of the dollar signs because I just instinctively press the F4 key.
So that's what you have to be kind of careful about.
All right, so we're done with that argument.
Press comma and then I can remember that it's the second row comma False because we're doing an exact match.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3,2,FALSE) All right and we're good to go.
We can copy that down.
Do you have to learn this syntax?
Heck no, you don't have to learn this syntax at all.
The apostrophe, open square bracket-- don't even worry about that.
You just have to go point to it.
Now one thing that's a little bit frustrating is that you have to-- once you switch over to that other workbook, they don't switch you back.
So if that's an issue for you, if the third or fourth or fifth arguments are something you want to point to here, you can always use Ctrl + Tab to get back but you could also do view-- either view side by side-- I always use ‘Arrange All’ and choose either horizontal or vertical.
I'll go with horizontal today.
Alright, so now that I can see both workbooks, I can do =HLOOKUP that cell comma and then just use the mouse down here.
=HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3 I don't have to press F4 comma 2 =HLOOKUP(A2,’[ Book3 ]Price List’!$A$1:$AC$3,2 and then if I needed to point-- I mean it's silly in this case.
If I needed to point to some cell back up here, I could actually point to that cell because I can see both workbooks.
So the ‘Arrange All’ kind of-- will help you if you have to point to something in the other workbook and then come back and point to, in a later argument the original workbook.
You know, it certainly seems daunting if you have to know how to create this syntax, to type that syntax but no one really types that syntax.
I just point to it using the mouse or the arrow keys Ctrl + Tab here.
Easy.
Hey, I want to thank Ron for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
