Spreadsheets

Compare two statement tabs in Google Sheets for unmatched rows

Compare two tabs in Google Sheets to find unmatched rows: build a match key, flag with COUNTIF or XLOOKUP, list them with FILTER, and use IMPORTRANGE across files.

Editorial illustration for Compare two statement tabs in Google Sheets for unmatched rows

To compare two sheets in Google Sheets and find differences, give every row a match key, check each key against the other tab with COUNTIF or XLOOKUP, and list the unmatched rows with FILTER on a Review tab. For statements in two files, bring one into the other with IMPORTRANGE first.

How do you build a match key?

A key joins the fields that make a row unique. Suppose each tab has date in column A, description in B and amount in C. In D2 on both tabs:

=TEXT(A2,"yyyy-mm-dd")&"|"&ROUND(C2,2)

That produces something like 2026-11-03|18000. Skip the description because narrations rarely match word for word between a bank and your ledger. To fill a whole column in one go, put this in D2 instead: =ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"yyyy-mm-dd")&"|"&ROUND(C2:C,2))), and delete the cells below it.

How do you flag a row that is missing from the other tab?

Three routes work, and the first is the simplest. On the Bank tab, in E2:

  • COUNTIF: =IF(COUNTIF(Ledger!$D$2:$D$500, D2)=0, "Only in Bank", "Both")
  • VLOOKUP: =IFERROR(VLOOKUP(D2, Ledger!$D$2:$D$500, 1, FALSE), "Not found"). This returns the key itself when found.
  • XLOOKUP: =XLOOKUP(D2, Ledger!$D$2:$D$500, Ledger!$B$2:$B$500, "Not found") returns the matching description, which is useful for eyeballing pairs.

Repeat on the Ledger tab with the ranges swapped to find rows that are only in the ledger. COUNTIF is quickest to read; XLOOKUP is best when you want to see the matched text alongside.

A short worked picture helps. Say the Bank tab has three rows with keys 2026-11-03|18000, 2026-11-05|640 and 2026-11-09|950, and the Ledger tab has 2026-11-03|18000 and 2026-11-09|590. COUNTIF finds the first key on both sides and calls it Both. The second and third keys are not in the ledger, so both come back Only in Bank, and the ledger's 590 row is Only in Ledger. That gives you two things to chase on the bank side and one on the ledger side, and the 950 versus 590 pair looks like one slip counted twice.

How do you list only the unmatched rows?

On a new tab called Review, in A1:

=IFERROR(FILTER(Bank!A2:E500, Bank!E2:E500="Only in Bank"), "Nothing unmatched")

FILTER keeps the rows where the test is true. Put the same formula with Ledger in a second block lower down or in another tab. The IFERROR wrapper stops the error that FILTER shows when nothing qualifies, so a clean month shows a clear message instead of a red cell.

What if the two statements are in different files?

Use IMPORTRANGE to pull the other file's key column into this one. In a spare tab called Other, cell A1:

=IMPORTRANGE("paste the other file's share link here", "Ledger!D2:D500")

The first time, Sheets shows a #REF! error and an Allow access button. Click it once. After that, point COUNTIF at the imported range: =IF(COUNTIF(Other!$A$1:$A$499, D2)=0, "Only in Bank", "Both"). Importing once into a helper tab is lighter than calling IMPORTRANGE inside every row.

Why does a visible match come back as not found?

  1. A date typed as text. TEXT(A2,"yyyy-mm-dd") may then return the original text, not a normalised date.
  2. Amounts that differ in paise. ROUND(C2,2) fixes most of it.
  3. Extra spaces in descriptions, if you included them in the key. Wrap them in TRIM.
  4. Sheets ranges that stop early, such as $D$2:$D$100 when your data runs to row 250.

A routine you can repeat

Keep the same layout every month: paste the new statement into the Bank tab, extend the key formula, and check that Review lists only genuine differences. Write the reason beside each one. After two or three months you will notice the same few causes, such as cheques that clear late or small bank charges you never recorded, and you can start adding them to the ledger as they appear.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports