Using VLOOKUP between two files works the same way as a single-workbook formula, except table_array needs to reference the second file's name and sheet directly. The practical difference is mostly about keeping both files open, or handling the longer reference needed when one is closed.
This page covers the setup using an order list split across two files, and a way to avoid managing file paths altogether.
Say Orders.xlsx contains the order list below, and a second file, StatusTracker.xlsx, needs to pull the Status column into its own sheet based on Order ID.
| Order ID | Customer Name | City | Status |
|---|---|---|---|
| ORD-501 | Rohan Gupta | Chennai | Shipped |
| ORD-502 | Meera Pillai | Bengaluru | Pending |
| ORD-503 | Arjun Nair | Hyderabad | Delivered |
With both files open, click into Orders.xlsx while typing the formula in StatusTracker.xlsx to build the reference automatically:
If Orders.xlsx is closed, the reference needs its full folder path included, not just the file name in brackets, e.g. 'C:\Data\[Orders.xlsx]Sheet1'!A2:D4 - the path is added automatically the first time the formula is built with both files open.
If Orders.xlsx is later moved to a different folder or renamed, every formula referencing it breaks and shows #REF! until manually repointed, which becomes tedious across a large tracker file with many formulas.
Turbo Excel VLookup sidesteps this entirely - both files are loaded fresh at the time of each match, so there's no stored path to break if either file moves. Choose the Order ID columns to match and the columns to pull across, then run the match.
When a VLOOKUP formula that used to work suddenly returns the wrong value with no visible error, the most likely cause is that a column was inserted or deleted inside table_array, shifting col_index_num out of alignment with the intended column.
Selecting the formula and pressing F9 with just that part of the formula highlighted shows the calculated value inline, which is a quick way to confirm whether table_array is still pointing where it should.
A common source of confusing errors is editing the same table that a VLOOKUP formula is pulling from, especially when rows are sorted or filtered mid-edit. Keeping a clearly labeled, rarely edited "source" sheet that formulas pull from, separate from a "working" sheet where day-to-day changes happen, reduces the chance of a lookup silently pulling from a half-edited row.
How is a two-file VLOOKUP formula different from a normal one?
table_array includes the second file's name and sheet in square brackets before the range.
Do both files need to be open for the formula to work?
No, but the reference needs the full folder path if the source file is closed.
What causes #REF! errors in cross-file formulas?
Moving or renaming the source file after the formula was created, which invalidates the stored path.
Is there an easier way to build the cross-file reference?
Yes, opening both files and clicking into the source file while typing the formula writes the reference automatically.
Does performance suffer with many cross-file formulas?
Yes, especially when the source file is closed, since Excel has to access it in the background for each recalculation.
How can I avoid managing file paths for two-file matching?
Turbo Excel VLookup loads both files fresh at match time instead of storing a permanent file path.
Is it a good idea to edit source data while formulas are actively referencing it?
It's safer to keep a stable source sheet separate from a working sheet, since editing referenced data mid-use can produce confusing intermediate results.
Why would a text-formatted number fail to match a real number?
Excel treats text and numeric values as different data types, so "101" stored as text won't match 101 stored as a number even though they display the same.
Does locking a range with F4 change how the formula calculates?
No, it only fixes the range so it doesn't shift when the formula is copied - the calculation itself works the same either way.
What's a fast way to check what a formula is actually returning?
Selecting part of the formula in the formula bar and pressing F9 shows its calculated value directly, without changing the formula.
Ready to stop fighting #N/A errors and match your spreadsheets visually?