Vlookup to Other Workbooks – Managing, Updating, & Sharing Files with Formula Links

Bottom Line: Learn how to create Vlookup formulas to other workbooks. Also find out how to manage and update formula links when the files are moved, and how to share the files that contain formula links with other users.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

Both of the files that I use in the video can be downloaded here:

Using Other Workbooks in VLOOKUP Formulas

In this post, we're going to take a look at how to write VLOOKUP formulas to other workbooks. These techniques will work with XLOOKUP, Index Match, or any other formula where you want to bring in data from a separate workbook.

First, we'll look at how to write the formula. Then we'll see what happens when we move the files to other folders and share files with other users.

Writing the VLOOKUP Formula

If you've never written a VLOOKUP formula, this post explains it step by step: VLOOKUP Example Explained at Starbucks. You can also watch this YouTube tutorial: VLOOKUP Tutorial for Excel – Everything You Need To Know.

In our example today, we're writing a VLOOKUP formula that looks up a product name in a table that's in another workbook and returns the category.

To write the formula, type the equals sign ( = ) and the word VLOOKUP and then tab into the selection when you see it. Our first argument is the lookup value. For our example, that's cell C4. The second argument is the table array. Here's where we select a table range from another workbook. Once we've selected that range, you'll see the reference in the formula. It always starts with the file name in square brackets, then the sheet name, and then the range reference. (If you're using an Excel Table for your source, the reference will show table name and potentially the column names as well.)

The third argument is the column index number. We're interested in the second column, so we would type the number 2. For our last argument, we will type the word “false” so that we are looking for an exact match.

So for our example, our formula reads:

=VLOOKUP(C4, ‘[Lookup Tables.xlsx]Categories'!$A$4:$B$20,2,FALSE)

When we hit Enter, our cell will show the category that VLOOKUP found in the table from the other workbook.

Some Things to Note

A great thing about this is that it will update automatically if there are changes to the source data or the lookup value. This is true even if the source workbook is closed.

Note: The automatic link updating can be turned on/off for the workbook by going to File > Options > Advanced > When calculating this workbook: > Update links to other documents checkbox. The setting is on by default and is a workbook level setting.

Another thing to note is that once we close the source workbook, the reference will automatically change to show the full file path, indicating where the file is located. If you're creating multiple references in a formula to another workbook, the formula can get pretty long.

If you want to reopen the source file that you've closed, use the Edit Links button on the Data tab. It shows a list of any linked files and you can click on the Open Source button to see the file that is being linked to.

When Files Move to Different Folders

If both the file that has the VLOOKUP formula and the source file get moved to the same folder, Excel is pretty good about recognizing that change and referencing the file in the new folder. Essentially no changes have to be made in that case. (You might have a security setting that requires you to click a button that says Enable Content.)

However, if only the source file gets moved, or both files get moved to different locations, it will take a bit more to update the links to work right.

When you open the VLOOKUP file after moving the source file, you will get an error message like this:

You might be tempted just to hit Continue, and keep working, but the links will be broken and the values in the cells won't update. Instead you want to choose the Edit Links option. This will again open the Edit Links window and you have the option to update the link by clicking on Change Source. From there you can just navigate to the new location and select the source file.

Sharing Files with Other Users

If you're going to be sharing these files and sending them via email, when your users or coworkers open the file, they're going to get the error message shown above. They would have to save both of the files to their own computer and then use the Edit Links Window to point to the correct file locations. This process is not necessarily something you want to put them through, right? There are a few ways to work around this problem.

1. Add Source Data to Your Workbook

One way is to just take the source range and move it into the same workbook as the formula. Once they are together in the same sheet, you can write the formula so that it doesn't require a link to another source.

2. Break the Links

If that's not feasible for your situation, another option might be to break the links. Then you can just send your users a workbook with values instead of formulas. Of course, this removes the dynamic aspect of the data. The values would not automatically update if there are changes to the source data.

You can break a link from the Edit Links Window.

3. Use a Shared Source Location

Another option for sharing, especially among coworkers, is to keep the files on a shared drive. Or better yet, use a team collaboration program like SharePoint or OneDrive. Then everyone is mapped to the same source for the links and changes wouldn't be needed.

Conclusion

I hope this post is helpful for you. If you have any questions or suggestions about linked workbooks or writing these formulas, please leave a comment below. Until next time, have a great week!