How to reconcile two Excel™ files by key, not by row order
Sort a ledger and a bank export, then read down both. It works until one side has an extra row. From that row on, every line is compared with the wrong partner and every line looks like a break.
The fix: match on a key
A key is a column that identifies a row in both lists, such as an invoice number. Match on that, and row order stops mattering.
Do it by hand
These steps apply to Excel™ in Microsoft 365 (XLOOKUP is not in older versions). Put both lists in one workbook, one sheet each.
- On the left sheet, add a helper column:
=XLOOKUP(A2, Right!A:A, Right!B:B, "Only on left"). Here A holds the key and B the amount on the right sheet. - Add a second column:
=IF(ISNUMBER(C2), C2-B2, "")for the difference. - Filter for "Only on left" and for non-zero differences. Those are your breaks.
- Do the same from the right sheet back to the left. Rows that appear only on the right never show up in step 1.
- Check for duplicate keys with
=COUNTIF(A:A, A2). A key appearing twice will match only the first hit.
Keep the original sheets untouched and put your working on a new one.
What a match does not tell you
A matching key with a different amount is a difference. A key present on one side only is a missing row. A key appearing twice needs a human. Keep those three groups apart.
Let it do the matching
Two-File Reconciliation matches two tables in a workbook on a key you choose and puts every row of both files in exactly one of four buckets, then writes the result as new sheets, so your source data is not changed. The free tier covers up to 1,000 rows a side with a single key column. The matching runs inside Excel™.
See Two-File Reconciliation for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Microsoft.