Look Who it Was Random video

Excel - LOOKUP to Another Workbook: Episode 1650

3,951 views · Published 21 February 2013 · 3:26 · Indexed 6 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Using LOOKUP Function to Retrieve Data from Another Workbook.

Welcome back to the MrExcel podcast. In today's episode, we will be discussing how to use the LOOKUP function to retrieve data from another workbook. This question was sent in by Ron, and it's a common issue that many Excel users face.

So, let's say we have our data in one workbook, and our LOOKUP table in another workbook. How do we create a LOOKUP formula that can retrieve data from the other workbook? Well, it's actually quite simple. First, we need to switch to the other workbook by using the Ctrl + Tab shortcut. Then, we can use the arrow keys or the mouse to navigate to the LOOKUP table.

Now, here's where things get a little tricky. When we usually create a LOOKUP formula, the table is usually located next to our data. But in this case, we need to navigate to the other workbook. So, we use the Ctrl + Shift + down arrow and Ctrl + Shift + right arrow shortcuts to get to the table. The formula will look something like this: =HLOOKUP(A2,'[Book3]Price List'!$A$1:$AC$3,2,FALSE).

One thing to be careful about is the use of dollar signs in the formula. When we switch to the other workbook, the dollar signs are automatically inserted to lock down the columns. So, we don't need to press the F4 key. However, out of habit, we may end up pressing the F4 key, which can cause errors in the formula. So, it's important to be mindful of this.

If you find it difficult to switch back and forth between workbooks, you can use the 'Arrange All' feature to view both workbooks side by side. This will make it easier to point to cells in the other workbook while building the formula. And remember, you don't have to memorize the syntax of the formula. You can simply use the mouse or arrow keys to point to the cells in the other workbook.

I hope this tutorial was helpful in understanding how to use the LOOKUP function to retrieve data from another workbook. Don't forget to thank Ron for sending in this question and for stopping by the MrExcel podcast. We'll see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) LOOKUP to Another Workbook
(00:11) Creating a HLOOKUP between two workbooks
(00:21) Navigating to the LOOKUP table in the other workbook
(00:31) Pointing to the table in the other workbook
(00:52) Using Ctrl + Tab to switch between workbooks
(01:03) Using Ctrl + Shift + arrow keys to select the table
(01:13) Be careful with the F4 key
(01:37) Completing the formula
(01:54) No need to learn the syntax
(02:04) Switching back to the original workbook
(02:14) Using ‘Arrange All’ to view both workbooks
(02:30) Pointing to cells in both workbooks
(03:05) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Ctrl + Tab
Dollar signs in formulas
Formula building
HLOOKUP
Lookup function
Navigation keys
Pointing with mouse or arrow keys
Syntax in Excel
Workbook switch


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153472/


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! http://www.mrexcel.com/learn2010/LE2010.html 

"The Learn Excel from MrExcel Podcast Series"

Visit us: MrExcel.com for all of your Microsoft Excel Needs!

More from this channel