How to Perform a VLOOKUP from a Different Workbook in Excel

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

1. Keep Paths Consistent: Ensure that the file path to the source workbook remains unchanged to avoid broken links.
2. Named Ranges: Consider using named ranges in the source workbook for more readable and manageable formulas.
3. Workbook Organization: Keep related workbooks in the same folder to simplify path management.
4. Error Handling: Use IFERROR to 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