Yes, you can cross reference two Excel spreadsheets. But if you’re copying data manually or using nested IFs across workbooks, you’re setting yourself up for version drift and #REF! errors.
The Setup
You’re managing vendor payments for Acme Corp. One file —
Payments_2024.xlsx — holds payment records. Another —
Vendors_Master.xlsx — holds verified vendor details: tax ID, address, and status. You need to pull the
Status and
Tax_ID from the master list into each payment row — but only where the
Vendor_Name matches.
Here’s what
Payments_2024.xlsx looks like (Sheet1, A1:E9):
| ID | Vendor_Name | Amount | Date | Status |
|---|
| P-101 | BrightLine Logistics | $12,450 | 2024-03-15 | |
| P-102 | Nexus Tech Solutions | $8,760 | 2024-03-18 | |
| P-103 | Veridian Builders | $24,100 | 2024-03-20 | |
| P-104 | Orion Media Group | $5,320 | 2024-03-22 | |
| P-105 | Stellar Labs Inc. | $16,890 | 2024-03-24 | |
| P-106 | TerraForm Holdings | $9,200 | 2024-03-26 | |
| P-107 | Aurora Design Co. | $3,750 | 2024-03-27 | |
| P-108 | Zenith Supply Chain | $11,300 | 2024-03-29 | |
And here’s
Vendors_Master.xlsx (Sheet1, A1:D10):
| Vendor_Name | Tax_ID | Address | Status |
|---|
| BrightLine Logistics | TX-7742-918 | 421 Harbor Dr, Seattle WA | Active |
| Nexus Tech Solutions | TX-3310-405 | 1900 Pine St, Austin TX | Active |
| Veridian Builders | TX-8821-776 | 77 Oakridge Blvd, Denver CO | On Hold |
| Orion Media Group | TX-2299-102 | 3030 Market St, Philadelphia PA | Active |
| Stellar Labs Inc. | TX-5544-881 | 1200 Research Pkwy, San Diego CA | Active |
| TerraForm Holdings | TX-6600-332 | 8800 Greenway Rd, Miami FL | Inactive |
| Aurora Design Co. | TX-1122-556 | 220 Park Ave, New York NY | Active |
| Zenith Supply Chain | TX-9900-773 | 555 Commerce Way, Chicago IL | Active |
| Horizon Data Systems | TX-4433-221 | 6000 Tech Loop, Portland OR | Active |
| Lumina Imaging LLC | TX-7788-992 | 1100 Med Plaza, Boston MA | On Hold |
The Challenge
You need to fill column E (
Status) in
Payments_2024.xlsx with matching values from column D of
Vendors_Master.xlsx. That sounds simple — until you realize:
• The files are saved separately (not in the same workbook)
• Vendor names might have extra spaces or inconsistent capitalization (e.g., “nexus tech solutions” vs “Nexus Tech Solutions”)
• You’ll want to pull
two fields (
Tax_ID and
Status), not just one
• If a vendor is missing from the master list, you don’t want #N/A cluttering your report — you want “Not Found” or blank
Can you cross reference two Excel spreadsheets? Yes — absolutely. But doing it right means choosing the right tool for your version and use case.
Walking Through It
We’ll start with XLOOKUP — the cleanest option if you’re on Microsoft 365 or Excel 2021.
First, open both files. In
Payments_2024.xlsx, click cell E2. Type:
=XLOOKUP(B2,'[Vendors_Master.xlsx]Sheet1'!$A$2:$A$11,'[Vendors_Master.xlsx]Sheet1'!$D$2:$D$11,"Not Found",0)
That tells Excel: “Look for the value in B2 inside column A of Vendors_Master.xlsx (rows 2–11); if found, return the corresponding value from column D; if not, show ‘Not Found’.”
Now — here’s the counterintuitive tip:
Don’t close Vendors_Master.xlsx while building the formula. If it’s open, Excel auto-fills the full path. If it’s closed, the formula will include the full file path (like
C:\Data\Vendors_Master.xlsx) — which breaks if you move either file.
Press Enter. Then drag the formula down to E9.
To pull
Tax_ID into column F, click F2 and type:
=XLOOKUP(B2,'[Vendors_Master.xlsx]Sheet1'!$A$2:$A$11,'[Vendors_Master.xlsx]Sheet1'!$B$2:$B$11,"",0)
No “Not Found” this time — we’ll leave blanks instead.
Still on Excel 2019 or older? Use VLOOKUP — but add TRIM and EXACT logic first. In E2, try:
=IFERROR(VLOOKUP(TRIM(B2),'[Vendors_Master.xlsx]Sheet1'!$A$2:$D$11,4,FALSE),"Not Found")
Then press
Ctrl+
Shift+
Enter if you’re in an older version that requires array entry (though modern Excel usually handles it automatically).
Better yet: use Power Query. Go to
Data → Get Data → From File → From Workbook. Select
Vendors_Master.xlsx. Load it as a connection only (don’t load to worksheet). Then go back to Payments_2024.xlsx, select your data range (A1:E9), and choose
Data → From Table/Range. In Power Query Editor, go to
Merge Queries → Merge Queries as New. Choose
Vendor_Name from both tables. Expand the merged column and pick
Status and
Tax_ID. Click Close & Load.
This method survives file moves, updates automatically, and handles duplicates gracefully.
The Result
After applying XLOOKUP, here’s your updated
Payments_2024.xlsx Sheet1 (A1:F9):
| ID | Vendor_Name | Amount | Date | Status | Tax_ID |
|---|
| P-101 | BrightLine Logistics | $12,450 | 2024-03-15 | Active | TX-7742-918 |
| P-102 | Nexus Tech Solutions | $8,760 | 2024-03-18 | Active | TX-3310-405 |
| P-103 | Veridian Builders | $24,100 | 2024-03-20 | On Hold | TX-8821-776 |
| P-104 | Orion Media Group | $5,320 | 2024-03-22 | Active | TX-2299-102 |
| P-105 | Stellar Labs Inc. | $16,890 | 2024-03-24 | Active | TX-5544-881 |
| P-106 | TerraForm Holdings | $9,200 | 2024-03-26 | Inactive | TX-6600-332 |
| P-107 | Aurora Design Co. | $3,750 | 2024-03-27 | Active | TX-1122-556 |
| P-108 | Zenith Supply Chain | $11,300 | 2024-03-29 | Active | TX-9900-773 |
What Could Go Wrong
Three real mistakes I’ve debugged dozens of times:
1. Case-sensitive mismatches
XLOOKUP and VLOOKUP are case-insensitive by default — but if your master list uses “NEXUS TECH SOLUTIONS” and your payments list says “nexus tech solutions”, leading/trailing spaces break the match. Fix: wrap both sides in TRIM(), or better — use Power Query’s
Text.Trim() during import.
2. Broken links when files move
If Vendors_Master.xlsx was closed when you built the formula, Excel embeds the full file path. Move either file? All formulas turn to #REF!. Solution: Keep both files open during setup, or switch to Power Query — it stores relative paths if both files live in the same folder.
3. Accidentally referencing the wrong sheet tab
It’s easy to type
[Vendors_Master.xlsx]List instead of
[Vendors_Master.xlsx]Sheet1. Excel won’t warn you — it’ll just return #REF! or zero. Always double-check the sheet name in the formula bar, and use the mouse to click-select the range instead of typing it.
Next Steps — Your Shortcut Cheat Sheet
| Task | Shortcut | Notes |
|---|
| Open Go To dialog (to jump to named range or cell) | F5 or Ctrl+G | Type [Vendors_Master.xlsx]Sheet1!A1 to navigate directly |
| Edit formula in formula bar | F2 | Critical when adjusting external references |
| Toggle between relative/absolute refs | F4 | Press once = $A$1, twice = A$1, thrice = $A1, four times = A1 |
| Refresh all external links | Alt → D → L → R | Updates links without reopening source files |
