Doing a VLOOKUP between two different Excel files mostly comes down to window management - both files need to be visible or easy to switch between while the formula is built, since the reference to the second file is created by clicking into it, not typed from memory.
This page walks through the practical setup using an inventory table, plus how to avoid the window-switching altogether.
Open both files, then use View > Arrange All > Side by Side (or Tiled) so both windows are visible simultaneously. This makes it much easier to click into the second file while the formula is being typed in the first.
Say Inventory.xlsx (source) has this item table:
| Item Code | Item Name | Warehouse | Stock Qty |
|---|---|---|---|
| ITM-01 | Steel Bolts | WH-A | 1200 |
| ITM-02 | Rubber Gasket | WH-B | 450 |
| ITM-03 | Copper Wire | WH-A | 980 |
In the second file, type =VLOOKUP( followed by the lookup cell, then click over into Inventory.xlsx and select A2:D4:
After pressing Enter, click back into the formula cell and check the formula bar - a correctly built cross-file reference always shows the source file name in square brackets followed by the sheet name and an exclamation mark.
If Inventory.xlsx is moved or renamed, Excel will typically prompt to update the source file location the next time the formula file is opened - accepting that prompt and pointing to the new location fixes all affected formulas at once.
Turbo Excel VLookup removes the need for side-by-side windows or manual formula building - load Inventory.xlsx as the Lookup Table and the other file as the Base File, choose the matching Item Code columns, and run the match.
A worksheet with thousands of VLOOKUP formulas recalculating against a large table_array can slow Excel down noticeably, especially if the range isn't locked and Excel has to re-evaluate a shifting reference on every keystroke.
Converting the source range into an Excel Table (Insert > Table) before referencing it in VLOOKUP tends to help, since Table references automatically resize with the data and stay consistent without needing manual $ locking.
Before applying a new VLOOKUP formula to an entire column, it's worth testing it on a single row and manually verifying the result against the source data, since a mistake caught in one cell takes seconds to fix, while the same mistake copied down five hundred rows takes much longer to untangle.
This is especially worth doing whenever the source table_array has changed shape recently, since a resized or reordered table is the single most common reason a previously reliable formula starts returning unexpected results.
Do the two Excel files need to be open at the same time to build the formula?
It's easiest with both open, since clicking into the second file writes the correct reference automatically.
How can I check my two-file VLOOKUP was built correctly?
Click the formula cell and confirm the formula bar shows the source file name in brackets and the correct sheet name.
What happens if the source file is moved after the formula is created?
Excel typically prompts to relocate the source file the next time the workbook is opened.
Is there a way to view both files side by side?
Yes, View > Arrange All > Side by Side in Excel arranges both open windows simultaneously.
Does the formula still work if I close the source file after building it?
Yes, though it then needs the full folder path stored in the reference to keep working.
Is there a way to avoid window management for two-file matching?
Yes, Turbo Excel VLookup loads both files directly without needing them arranged on screen together.
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.
Can trailing spaces in a cell cause a lookup to fail?
Yes, an extra space at the end of a value is invisible but prevents an exact match; TRIM() around the lookup value often fixes this.
Ready to stop fighting #N/A errors and match your spreadsheets visually?