Excel’s VLOOKUP function is a powerful tool for searching and retrieving data from a specific column in a table or range. While using VLOOKUP within the same workbook is common, there are times when you need to pull data from a different workbook. This can be particularly useful when working with large datasets spread across multiple files. In this guide, we’ll walk you through the steps of using VLOOKUP to reference data in another workbook.
Step-by-Step Guide to Using VLOOKUP Across Workbooks
Step 1: Open Both Workbooks
First, you need to have both the source workbook (the one containing the data you want to look up) and the destination workbook (the one where you want to perform the VLOOKUP) open in Excel.
Step 2: Identify the Lookup Value
In your destination workbook, decide which cell will contain the lookup value. This is the value that you will use to search in the source workbook.
Step 3: Begin the VLOOKUP Function
In the cell where you want the VLOOKUP result to appear, start the function by typing:
=VLOOKUP(
Step 4: Enter the Lookup Value
Specify the lookup value, which can be a cell reference. For example:
=VLOOKUP(A2,
Here, A2 is the cell in your destination workbook that contains the lookup value.
Step 5: Specify the Table Array from the Source Workbook
This step is crucial and a bit different from the usual VLOOKUP. With both workbooks open, switch to the source workbook and select the range of cells that contains the data you want to look up. Excel will automatically insert the reference to this range in your formula.
For example, after selecting the range, your formula might look something like this:
=VLOOKUP(A2, '[SourceWorkbook.xlsx]Sheet1'!$A$2:$B$11,
Step 6: Define the Column Index Number
Enter the column index number from which you want to retrieve the data. This number represents the column in the source workbook’s range. For instance, if you want to retrieve data from the second column in the specified range, you would enter:
=VLOOKUP(A2, '[SourceWorkbook.xlsx]Sheet1'!$A$2:$B$11, 2,
Step 7: Specify the Range Lookup Value
Decide whether you need an exact match or an approximate match. For an exact match, use FALSE. For an approximate match, use TRUE. Typically, you will use FALSE for exact matches:
=VLOOKUP(A2, '[SourceWorkbook.xlsx]Sheet1'!$A$2:$B$11, 2, FALSE)
Step 8: Complete the Formula
Press Enter to complete the formula. Excel will return the corresponding value from the source workbook based on the lookup value and copy paste it all other rows. (Ctrl+C) to copy & (Ctrl+V) to paste
Step 9: Handle External References
If you close the source workbook, Excel will automatically convert the reference in your formula to an external reference, which will look something like this:
=VLOOKUP(A2, 'C:\Users\YourUsername\Documents[SourceWorkbook.xlsx]Sheet1'!$A$2:$B$11, 2, FALSE)
Step 10: Update Links if Needed
If you move or rename the source workbook, you’ll need to update the link in your destination workbook. You can do this by going to the Data tab, clicking on “Edit Links,” and updating the source path.
Tips for Using VLOOKUP Across Workbooks
- Keep Paths Consistent: Ensure that the file path to the source workbook remains unchanged to avoid broken links.
- Named Ranges: Consider using named ranges in the source workbook for more readable and manageable formulas.
- Workbook Organization: Keep related workbooks in the same folder to simplify path management.
- Error Handling: Use
IFERRORto handle cases where the lookup value might not be found=IFERROR(VLOOKUP(A2, '[SourceWorkbook.xlsx]Sheet1'!$A$2:$B$11, 2, FALSE), "Not Found")
By following these steps, you can effectively use VLOOKUP to pull data from another workbook, streamlining your data management and enhancing the efficiency of your workflow. Whether you’re consolidating data from multiple sources or referencing a master dataset, mastering this technique will significantly boost your Excel skills.
For More: Microsoft Excel