How to reconcile two Google Sheets™ with different column order

By Paul Kemp · Published 24 September 2026 · Updated 2 October 2026

The ledger has Date, Invoice, Customer, Amount. The bank export has Reference, Amount (GBP), Value date, Memo, in a different row order, with a few rows the other side never heard of. Sorting both and reading down them fails at the first extra row. Here is the method that works, by hand in Google Sheets™, and then the shortcut.

1. Put both tables in one spreadsheet

Right-click the bank tab in its own file, choose Copy to › Existing spreadsheet, and pick the ledger's spreadsheet. The original file stays untouched, which matters: reconciliation inputs are evidence. Avoid IMPORTRANGE for this. It makes a live link, so next month's check would quietly compare against whatever is there then.

2. Match on a key, not on position

Pick the column that identifies the same thing on both sides, usually the invoice number or reference. Column order and column names stop mattering the moment you look rows up by that key instead of reading across.

Real keys are rarely written the same way twice: INV-0012 in the ledger, inv 12 or INV12 at the bank. Add a helper column on each tab that cleans the key the same way. It lower-cases, drops everything but letters and digits, and removes leading zeros from numbers:

=REGEXREPLACE(REGEXREPLACE(LOWER(TRIM(TO_TEXT(B2))), "[^a-z0-9]", ""), "(^|[^0-9])0+([0-9])", "$1$2")

All three of those references become inv12. Fill it down both tabs (on the bank tab, point it at the reference column).

3. Look each row up on the other side

With the cleaned key in column E of both tabs, bring the bank amount across to the ledger:

=XLOOKUP(E2, Bank!E:E, Bank!B:B, "missing")

and the difference beside it:

=IF(F2="missing", "", ROUND(D2 - F2, 2))

"missing" rows are on the ledger only.
Do the same lookup the other way on the bank tab to find rows on the bank only.
A non-zero difference is a real break. ROUND stops floating-point noise such as 0.0000001 from showing up as one.

4. Check for keys that appear twice

XLOOKUP returns the first match and says nothing about the second. Count them: =COUNTIF(Bank!E:E, E2). Anything above 1 needs a decision: a split payment to add up, or a genuine duplicate.

5. Prove the difference

The totals should reconcile exactly: ledger total − bank total = ledger-only rows − bank-only rows + the sum of the differences. If that does not come out to the penny, a key matched the wrong row or a duplicate went unnoticed. This one line is what goes in the month-end pack.

The shortcut

Two-File Reconciliation is a Google Sheets™ add-on that does all five steps. You pick the two tables, it proposes which columns line up, and it matches on the key with that clean-up built in. It then writes the four buckets (agree, differ, ledger only, bank only), the duplicates and the decomposed difference as new tabs. Your source tabs are never touched. Save the setup as a recipe and next month is one click. It is free up to 1,000 rows a side, and nothing leaves your spreadsheet.

Install: in Google Sheets™, Extensions › Add-ons › Get add-ons, search for “Two-File Reconciliation”. The guide walks through a first run. Using Excel? The Excel add-in does the same.

Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Microsoft and Excel are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Google or Microsoft.