Reconcile two files in Excel with XLOOKUP (the right way)
Most reconciliations start in Excel, and XLOOKUP does the heavy lifting. But the formula is the easy part. What actually trips people up is matching cleanly, catching what's missing (not just what differs), and not getting fooled by formatting that makes identical numbers look different. That last one has quietly eaten more evenings than any hard formula ever has.
The setup: two sheets, one shared key
Put each export on its own sheet — call them SystemA and SystemB. Confirm both have a column that identifies the same record: an order number, SKU, or transaction ID. That column is your key. Everything below assumes the key is in column A on each sheet and the value you want to compare (say, an amount) is in column B.
Step 1: Does each key exist on the other side?
Before comparing values, find the rows that have no counterpart. XLOOKUP returns its if-not-found argument when there is no match, which is exactly what you want.
=XLOOKUP(A2, SystemB!$A:$A, SystemB!$A:$A, "MISSING")Drag this down SystemA. Any row showing MISSING exists in A but not in B. Repeat the formula on SystemB pointing at SystemA to catch the rows missing the other way. Those two columns are your one-sided differences — the same set difference you can get with COUNTIF and MATCH.
Step 2: Where the key matches, do the values agree?
For rows that exist on both sides, pull the other side value and compare. Put this in column C:
=XLOOKUP(A2, SystemB!$A:$A, SystemB!$B:$B, "-")Then in column D, flag the difference:
=IF(C2="-","no match",IF(ROUND(B2-C2,2)=0,"ok","diff "&TEXT(B2-C2,"0.00")))Now column D reads ok, no match, or diff 12.50 for every row. Filter to anything that is not ok and you have your working list.
The formatting traps that create fake differences
| Trap | What you see | Fix |
|---|---|---|
| Numbers stored as text | Identical values will not match | Run the column through VALUE(), or multiply by 1 |
| Leading zeros lost | 0042 became 42 | Format key as text before import; compare as text |
| Trailing spaces | 1001 (with space) not equal to 1001 | Wrap the key in TRIM() |
| Hidden prefixes | #1001 vs 1001 | Use SUBSTITUTE() to remove the prefix on both sides |
| Rounding | 19.999 vs 20.00 | Compare with ROUND(...,2), never raw |
XLOOKUP vs VLOOKUP for reconciliation
VLOOKUP still works, but XLOOKUP is better suited to this job: it has a built-in not-found argument (no IFERROR wrapper), it looks left or right without counting columns, and it does not break when someone inserts a column. If you are on an older Excel without XLOOKUP, INDEX with MATCH is the equivalent: =INDEX(SystemB!$B:$B, MATCH(A2, SystemB!$A:$A, 0)).
Where the spreadsheet method runs out
Formulas reconcile two files just fine. They start to buckle the moment you've got three or more sources, the same comparison every week, a composite key, or someone who needs to audit how a number was reached. A workbook saves the answer but not the reasoning — and once other people have to trust that number, the reasoning has to live somewhere more repeatable than a cell formula.
Frequently asked questions
What is the best Excel function to compare two files?
XLOOKUP is the most reliable for reconciliation because it can return a custom not-found value, look in any direction, and survive inserted columns. INDEX with MATCH is the equivalent on Excel versions without XLOOKUP.
Why do identical-looking values show as different in Excel?
Usually because one is a number and the other is text, or there are trailing spaces, lost leading zeros, or hidden prefixes. Clean the key with TRIM and SUBSTITUTE, convert text-numbers with VALUE, and compare amounts with ROUND.
How do I find rows that exist in one file but not the other?
Use XLOOKUP with a MISSING fallback against the other sheet key column, then run it in both directions. Rows returning MISSING exist on only one side.