The good news is that a VLOOKUP between 2 workbooks is fairly straightforward – Excel will do the work for you as far as referencing the other workbook in the formula goes. There are a few rules to follow once you’ve closed and saved the workbooks but let’s focus on the VLOOKUP formula first in the way of an example. You can download these files and follow the example yourself.
VLOOKUP Example From One Workbook to Another
We have 2 example workbooks which live in the same folder
1. We have both workbooks open at the same time. Open first workbook and then press Ctrl O to open the second (this prevents a second instance of excel being opened in some versions of Excel.
2. In workbook 1 we select the cell where we want to do the VLOOKUP (in this case cell B2) and start typing the formula.
3. When we get to the second part of the formula (WhereToSearch), we select the other workbook and highlight the first 3 columns. As we do this, the formula updates automatically to reference the other workbook.
4. We press comma (,) and then click back on the first workbook and finish off the formula with the WhichColumn and NearOrExact parts of the formula (in this case “3, FALSE” ) and hit Enter and voila! We’ve done a lookup across 2 workbooks.
When we close Workbook 2 something interesting happens. The VLOOKUP formula automatically updates to include the folder location of Workbook 2.
An upcoming post on this site will provide some guidelines to follow so that we don’t lose the connection between these workbooks even though their locations might change.
Why might you need a VLOOKUP between 2 files?
You might have one master data book which has a whole lot of others feeding off it and you’d like to only be editing the master VLOOKUP tables in once place and have those changes reflect in the various workbooks feeding off the master.
Some Hot Tips If Either Master or Servant Workbook is Dynamic
(Master workbook contains the master tables from which the Servant workbooks do their lookups)
- If you want to save yourself the hassle of changing the path, keep the files in the same folder. That way whenever you open them up together, they’ll stay linked.
- You can email the files together too. Advise the recipient to open them both together and to save them to the same folder to keep the link alive.
- If you want to send just the workbook that’s feeding off the master and not the master itself, save the recipient some hassle by copying the VLOOKUP cells and pasting values (shortcut for pasting values is ctrl + e, s, v).
- If you want to change the Master Workbook to a different one, delete or move the original master file and when you open up the Servant Workbook that feeds off it, you’ll get a prompt for where the VLOOKUP should reference which is your chance to point to the new Master file.
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.