An XLOOKUP Alternative That Works Across Files
XLOOKUP is a real upgrade over VLOOKUP — it looks left, returns whole arrays, and doesn't care about column index numbers. But it's still a formula bound to a workbook. The moment your data lives in two separate files, or the same file lands again next month, XLOOKUP starts to creak: cross-workbook references break when a file is moved or renamed, and you rebuild the whole thing on every new export.
If your job is matching data across files, on a schedule, there's a sturdier way that you build once and re-run.
Where XLOOKUP still falls short
- Across workbooks it's fragile. A formula pointing at another file breaks if that file moves, is renamed, or isn't open.
- It's not repeatable. Next month's file means re-pointing or rebuilding the lookup from scratch.
- It's per-cell. Drag XLOOKUP down hundreds of thousands of rows and the workbook crawls.
- It can't span a folder. Applying the same lookup to twenty files is twenty manual jobs.
The repeatable approach: a merge step
Instead of a formula, use a merge: pick the key column that links the two files and choose which columns to bring across. It's the same mental model as XLOOKUP — match on a key, pull values — but it's a reusable step, not a brittle reference.
- Open your main file (e.g.
orders.csv). - Add a merge from another file step and choose the second file (e.g.
customers.xlsx) — a different workbook entirely. - Match
orders.CustomerIDtocustomers.ID. - Pick the columns to pull in: Name, Email, Region.
- Keep all rows (unmatched get blanks) or only matches.
| XLOOKUP across files | Kramata merge | |
|---|---|---|
| Match on a key column | Yes | Yes |
| Survives the other file moving/renaming | No | Yes (re-pick once) |
| Re-runs on next month's file | Rebuild | One click |
| Fast on 100k+ rows | Slows down | Yes (database) |
| Apply across a whole folder | No | Yes (batch) |
Across many files at once
Save the merge as part of a recipe and run it in batch across a folder — every file gets the same lookup applied, with no formula copied anywhere. That's the part XLOOKUP simply can't do.
Related guides
Merge two spreadsheets without VLOOKUP · Merge multiple CSV files into one · Spreadsheet recipes: build once, reuse forever
FAQ
Can I XLOOKUP between two separate workbooks? You can, but cross-workbook XLOOKUP is fragile — it breaks when a file moves or is renamed. A merge step matches the two files by a key column and is rebuilt automatically each run, so it stays reliable.
How is this different from VLOOKUP? Same job — pull columns from a second file by a matching key — but as a reusable step instead of a formula. It doesn't break when columns move and you don't rebuild it for each new file.
Does it work on multiple files at once? Yes. Save the merge as part of a recipe and run it in batch across a whole folder, so every file gets the same lookup applied.
Will it slow down on large files? No. Matching runs through an in-process database rather than a formula dragged down thousands of rows, so big files stay fast.