Do a VLOOKUP between two files
The orders file has the customer code, the customer file has the name and city. A VLOOKUP between the two brings them together, and leaves a link between the files behind.
In Excel, step by step
Open both files
The formula is easier to build when the reference file is open.
Write the formula
=VLOOKUP(A2,[Customers.xlsx]Sheet1!$A:$C,2,FALSE), clicking in the other file to select the range.Freeze the result
Once the column is filled, Copy then Paste Special, Values: the link to the other file disappears.
Pitfalls
- Without pasting as values, the file keeps a link to the other one, and the links message comes back at every opening.
- A code stored as a number on one side and as text on the other is not found: #N/A.
- Forgetting FALSE at the end of the formula gives wrong results with no error.
Faster, on the whole file
Look up between two files does VLOOKUP between two files with no formula and no link: the key is suggested automatically, spaces and numbers stored as text are neutralised, and keys not found are listed.
Frequently asked questions
- Why does my file ask to update links?
- Because a formula still reads the other file. See the external links message.