How do you compare two separate lists of detailed transactions and identify any difference?

Question

I have an excel spreadsheet with the daily transactions posted the bank and I have an excel spreadsheet from checkbook app. I would like to compare the two lists to identify any differences that I need to update in the checkbook app.

Answer 1

Standardize both lists and then use a lookup or comparison formula. Make sure both sheets have matching columns such as Date, Amount, and Description, and ideally create a helper column that combines them into a unique key so matching is reliable.

In both sheets, add a helper column like:

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

(adjust columns for date, amount, description). This creates a comparable transaction ID.

Then, in your checkbook sheet, use:

=IF(ISNA(XLOOKUP(D2, BankSheet!D:D, BankSheet!D:D)), "Missing in Bank", "Match")

where D is the helper column. This flags anything in your checkbook that does not exist in the bank data.

To find transactions in the bank that are missing from your checkbook, do the reverse on the bank sheet:

=IF(ISNA(XLOOKUP(D2, CheckbookSheet!D:D, CheckbookSheet!D:D)), "Missing in Checkbook", "Match")

If you want a cleaner output, you can filter only the differences:

=FILTER(A:D, ISNA(XLOOKUP(D:D, CheckbookSheet!D:D, CheckbookSheet!D:D)))

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin