To find transactions that are in one statement but not the other, count how many times each row's date, amount and reference appear in the other list. If COUNTIFS returns 0, the row is missing from the other side. Do it both ways, A against B and B against A, and filter on the flags to see only the differences.
What does the sample data look like?
Put list A on a tab called A, such as your bank statement, and list B on a tab called B, such as your own record. Each has date in column A, amount in column B and reference in column C. Here are six made-up rows each:
| List A: date, amount, ref | List B: date, amount, ref |
|---|---|
| 2 Nov, 1,250, R4411 | 2 Nov, 1,250, R4411 |
| 3 Nov, 18,000, R4412 | 3 Nov, 18,000, R4412 |
| 5 Nov, 640, R4413 | 7 Nov, 3,200, R4414 |
| 7 Nov, 3,200, R4414 | 9 Nov, 590, R4415 |
| 9 Nov, 950, R4415 | 12 Nov, 7,500, R4416 |
| 12 Nov, 7,500, R4416 | 13 Nov, 2,100, R4417 |
By eye you can already see R4413 is only on the left, R4417 only on the right, and R4415 has ₹950 on one side and ₹590 on the other, a classic swapped-digit slip.
How do you flag rows that are only in A?
On tab A, in D2, filled down for every row:
=IF(COUNTIFS(B!$A$2:$A$500, A2, B!$B$2:$B$500, B2, B!$C$2:$C$500, C2)=0, "Only in A", "In both")
The formula asks: in tab B, is there a row with the same date, same amount and same reference? Zero means no. Then, on tab B, use the mirror image in D2, pointing back at tab A:
=IF(COUNTIFS(A!$A$2:$A$500, A2, A!$B$2:$B$500, B2, A!$C$2:$C$500, C2)=0, "Only in B", "In both")
On the sample, A flags R4413 and R4415, and B flags R4415 and R4417. The R4415 appears on both sides because its amounts differ, so neither side finds an exact partner.
How do you see just the differences?
- Any Excel version: select the header row, press Ctrl+Shift+L to add filters, then tick only Only in A.
- Excel 365 or 2021: on a spare sheet, =FILTER(A!A2:D500, A!D2:D500="Only in A", "None") returns just the flagged rows and updates itself.
- Copy the flagged rows to a Review tab if you want to annotate each one.
FILTER is not available in Excel 2016 or 2019, so use the filter button there.
Why do obvious matches sometimes fail?
Almost always a data problem, not a formula problem. Check these in order:
- Dates stored as text on one side. Select the column and look at the alignment: real dates align right.
- Amounts with trailing spaces or a rupee symbol typed in, which makes them text.
- References that carry extra characters. Use =TRIM(C2) in a helper column if needed.
- Wildcards: COUNTIFS treats a star or a question mark inside a reference as a wildcard, so a reference containing either may match more than you expect.
- Date with a time part. Use =INT(A2) to strip the time.
What do you do with each flagged row?
Decide for each whether it is missing, mis-typed, or timing. A row only on the bank side is something you forgot to record. A row only on your side may be a payment that has not cleared. The pair with different amounts is a correction. Write the reason beside each so next month's review starts from your notes rather than from nothing.
If you reconcile the same two sources every month, SpendixAI offers a different route: upload the statement, review the rows, and it flags possible duplicate imports, so the same line does not enter twice.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


