Excel - Mastering VLOOKUP: How to Retrieve Data from an External Workbook in Excel - Episode 1082

1,978 views · Published 18 August 2009 · 3:17 · Indexed 31 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Mastering VLOOKUP: How to Retrieve Data from an External Workbook in Excel.

Welcome back to the MrExcel netcast! In this episode, we're going to tackle a common problem faced by many Excel users - how to use VLOOKUP to retrieve data from an external workbook. As we all know, Excel is a powerful tool for analyzing large amounts of data, but sometimes the data we need is stored in a different workbook. So, how do we go about solving this problem? Let's dive in and find out!

First, I want to give a shout out to our new viewer, Shawn, who recently discovered us on YouTube. Shawn had a question about linking worksheets to a table in an external workbook and asked if we had any videos covering this topic. Well, Shawn, this video is for you! We're going to show you the best way to build a VLOOKUP formula that retrieves data from a table in another workbook.

To demonstrate this, I have two workbooks open - one with the codes and rates we want to retrieve, and the other with the VLOOKUP formula. By having both workbooks open, we can easily reference the table in the external workbook. I'll show you how to do this step by step, so you can follow along and apply it to your own projects.

Now, let's take a closer look at the formula we built. You may have noticed some tricky elements, such as square brackets and an exclamation point. These are necessary when referencing a table in another workbook. Also, keep in mind that if there are any spaces in the workbook or worksheet names, you'll need to surround them with apostrophes. But don't worry, as long as both workbooks are open at the same time, you won't have any issues.

But here's the catch - the external workbook cannot have more than 10,000 cells linked to it. So, if your table is larger than that, your VLOOKUP formula won't work unless both workbooks are open. This is something to keep in mind when working with external workbooks. But for smaller tables, like the one we used in this example, there's no problem at all.

I hope this video has been helpful in showing you how to use VLOOKUP to retrieve data from an external workbook. Thanks for stopping by the MrExcel netcast, and be sure to tune in next time for more Excel tips and tricks. Don't forget to like and subscribe for more helpful videos. See you next time!

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) Analyzing Data with Pivot Tables
(00:15) Viewer Question: Linking Worksheets to External Workbook
(01:00) Building a VLOOKUP Formula
(01:36) Tricky Aspects of the Formula
(02:12) Potential Problem with Large Tables
(02:55) 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:
Absolute reference
Analyzing data
Exact match
Linking to an outside workbook
Pivot table
Table array
VLOOKUP formula
Workbook name
Worksheet name
Worksheets

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


Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.

More from this channel