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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919