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

Watch on YoutubeSubscribe to our Channel

Download the Excel File

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

Order Data.xlsx (34.3 KB)

Lookup Tables.xlsx (18.8 KB)

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.

VLOOKUP can pull data from one workbook into another
Click to enlarge

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.

VLOOKUP formula linking to another workbook

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.

Edit Links Window

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:

Warning message about links that can't be found

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.

Break Link button in 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!

  • Hi Jon,
    Thank you, this has cleared up a nagging question for me.
    I have templates and source files in a template folder which I plan to copy to a project folder, and I was hoping I don’t have to rewrite all the formulae.

  • Thank you Jon,

    This video was very helpful. I am new to Vlookup and trying to understand and learn to use this tool.
    Thanks again,

  • If you really must write formulae linking to other worksheets, link to a named range or structured table reference. That way, if the source file structure changes when the file-that-has-your-formula is closed, then the references will still have a chance of working (your VLOOKUP formula may be compromised though, that’s why you should use XLOOKUP or INDEX-MATCH)
    Better still, you could use PowerQuery to import a copy of the data you want to work on

    On moving files, rather than move the files then edit links, you can open both files, save them to the new location then close and delete the originals – I’d find that easier

  • Hi Jon. Thanks for this. I work with external links as part of my job but I have never known about the “Update links to other documents” setting before. I’m familiar with the “Startup Prompt” button in the Edit Links window, which lets you choose whether to update links whenever the destination workbook is opened, don’t update on open, or prompt user on open whether to update or not. I’m also familiar with VBA code to update links on certain events.

    Testing out “Update links to other documents” just now, I don’t see it making any difference. I turned off this setting, opened my source workbook in a separate Excel application, made a change and saved it and closed it. Then, if I have my destination workbook re-calculate formulas (by going to a formula and hitting F2 and Enter), it does not pick up the change I made to the source data, unless the formula I F2/Enter is itself a direct link to the source workbook. (It doesn’t work, for example, if I F2/Enter a formula that is dependent on a cell whose formula is a direct link to the source workbook.) This behavior is the exact same whether “Update links to other documents” is turned on or off.

    Another way it picks up the changed source data is if I change the lookup value. Again, this behavior is the exact same whether “Update links to other documents” is turned on or off.

    So what is the practical effect of the “Update links to other documents” setting?

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >