Vlookup Across Multiple Workbooks

How to use the VLOOKUP function across multiple workbooks in Excel. This will create a link between the workbooks that allows you to get updated data from the source-data workbook, even when it is closed.

Sections:

Before you Begin

Vlookup Between Multiple Workbooks

Potential Issue

Notes

Before You Begin

Make sure that the data in the source-workbook is setup correctly for a vlookup.

The column used to find the lookup_value should be the left-most column in the source data.

Vlookup Between Multiple Workbooks

Open both workbooks, the one that will have the vlookup and the one that has the source-data from which you will return values.

1. Go to the worksheet where you want to display the data using the Vlookup and start to enter the function.
   
2. Select the cell that contains the value for the first argument, the lookup_value. (The lookup_value is what is used to find the values in the source-data and it should be contained in the left-most column of the source-data table.)
   
   Once, you select the lookup_value, type a comma to go to the next argument.
3. The next argument is the table_array argument, and this is what is located in the other workbook.
   Without typing anything or hitting any other button, navigate to the source-data workbook and click and drag the mouse over the table that contains your data.
   
   Notice that the formula that we started entering on the original workbook has now appeared within the formula bar at the top of this one (if you do not see the vlookup formula appear at the top, go back to step 1 and start over).
   While still on this screen, type a comma and move to the next argument; the rest of the formula will be completed while viewing the source-data workbook.
4. You will see the argument list for the vlookup function appear under the formula bar and you should now see the col_index_num argument in bold.
   
   I want to get the value from the Quantity column, so I put a 3 for this argument, since the Quantity column is the third column in the source-data table.
   Don't get confused about finishing the formula on the source-data worksheet, this is just part of the process.
   Don't forget to type a comma to move to the last argument.
5. For the last argument, the range_lookup, we will use False so that only exact matches are returned; most of the time, this is what you want to use.
   
   You are now done entering the function; you can either hit Enter or first input the closing parenthesis ) and then hit Enter.
   Once you do this, you should be taken back to the first workbook, the one with the vlookup in it, and a value should be returned.
   
   That's it!

If you did not already enter a value for the lookup_value argument, cell A2 in the above example, then the vlookup will return an error by default; simply input a lookup_value to fix this.

Potential Issue

When you close the workbooks and open the one with the lookup formula, you might see some warnings. These warnings come about because the workbooks are now linked, which means that one pulls data from the other one.

In order to allow data to flow between these workbooks, you will have to click the button to enable the links when you see this warning.

Here is a sample warning that you might see:

And here is another one:

(Warnings don't always appear so don't get nervous if you don't see one.)

Notes

Getting data from other workbooks is one of the most useful things that you can do in Excel and, as you can see, it is not that difficult to do. Once you have the necessary workbooks open, it is, basically, just like using formulas between worksheets in the same workbook.

Make sure to download the sample files for this tutorial so that you can work with the above examples.