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
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.
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!
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.
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
If the sorce data is stored in a fixed location in the cloud, would this pose any problems?
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?
Excellent Example and easy to understand
[…] Vlookup to Other Workbooks – Managing, Updating, & Sharing Files with Formula Links […]
when i get the formula =vlookup(B2,’F:stock register[30151105.xlsm]sheet2′!F2000,1,true). then its work. but when i use the formula =vlookup(B2,’F:stock register[DD121181.xlsm]sheet2′!F2000,1,true). its not work. when [ ] name in digit its work, but when there is alphabet its not work. why?
please help me.
Vlookup does not automatically update when I open the worksheet with the Vlookup formula – because it can’t find the file. The file path looks correct, but for some reason the files (on my work network) can not be found. I always have to go to edit link and change the source and click on the correct file again.
This is so useful, thank you!
I’m having issues with my VLookup in a large doc I’ve created.
It’s pulling info from 2 different sources (I’m not able to copy this data into my main workbook) that are on a shared server. Whenever I close the main workbook or the source workbooks, then come back to my main one, ALL of my vlookup formulas are returning ‘#REF!’ values.
I’ve tried relinking the sources as shown in this video, but that’s not working.
At this point i’m tearing my hair out and contemplating throwing my laptop across the room! Please help! Do you have any suggestions?
Can I do a vertical lookup vs a (master) excel file to which some of the users of the (working) excel file containing the vlookup do NOT have access? I do would like the values in the working file(s) to update when users open that file, in case input in the master has changed.
I have set up a structure on the sharepoint, with individual access levels on the working files. Certain users have access to the entire Sharepoint folder including master and working files, certain users only have edit or view rights to the working files.
Thanks in advance
I have a master spreadsheet that many other spreadsheets reference via VLOOKUP functions. I need to add 5 columns to my master spreadsheet, but this means that all the LOOKUP functions are now referencing the wrong column. Is there any way to get all the other spreadsheets to update automatically?
Hi, do you know what is the frequency where the link is refreshed and if it could be changed?
Can Excel copy and paste Flagged Notes from one spreadsheet to another using a Function formula?
My Destination File where i Want the VLOOKUP to work is on a shared drive & from where i want to get the reference is on desktop. How to put VLOOKUP successfully on the shared drive.
I use this formula for all my files… I want something that can change the file name in the formula automatically I tried indirect but it is not working with closed file…. Is there any other formula or function that can work same way but also with closed files please?? It will resolve a big issue for me