Reconcile Two Lists (Find Mismatches)

Excel Formulas › Auditing & Error-Proofing

All versionsCOUNTIF

Two lists that should match — a bank export vs your ledger — rarely do. A COUNTIF against the other list flags every item that’s missing on one side, turning reconciliation into a one-column check.

Quick formula: flag items in list A missing from list B:
=IF(COUNTIF(list_b, A2) = 0, "Missing in B", "OK")
Count each A item in list B; zero means it isn't there. Run the mirror check from B against A too.

Functions used (tap for the full reference guide):

COUNTIFISNA

The example

Items present in one list but not the other.

AB
1ItemCheck
2INV-100OK
3INV-205Missing in B

The formula

The formula:

=IF(COUNTIF(list_b, A2) = 0, "Missing in B", "OK") // count in the other list

How it works

How it works:

  1. COUNTIF(list_b, A2) counts how many times an A item appears in B — 0 means missing.
  2. Run the mirror check: COUNTIF(list_a, B2)=0 to find items in B not in A.
  3. Together they surface everything that doesn’t reconcile on either side.
  4. Build a normalized key first if formats differ (case, spaces, leading zeros).

Reconcile both directions. A single COUNTIF only finds A-items missing from B; items in B that aren’t in A stay hidden. Always run the mirror check too. And when amounts should match per key, add a SUMIF comparison — same key, different totals is a different (and common) reconciliation failure than a missing key.

Try it: interactive demo

Live demo

List A and list B (comma-separated).

Variations

Mirror check

Items in B not in A:

=IF(COUNTIF(list_a, B2)=0, "Missing in A", "OK")

Amount mismatch

Same key, diff total:

=SUMIF(a_key,key,a_amt) - SUMIF(b_key,key,b_amt)

Count unmatched

How many off:

=SUMPRODUCT(--(COUNTIF(list_b, list_a)=0))

Pitfalls & errors

Check both directions. One COUNTIF misses items unique to the other list.

Normalize keys. Case, spaces, and leading zeros cause false mismatches.

Amounts too. Matching keys can still have differing totals — compare with SUMIF.

Practice workbook

📊
Download the free Reconcile Two Lists (Find Mismatches) practice workbook
A reconciliation sheet with the mirror, amount-mismatch, and count variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I reconcile two lists in Excel?
Flag missing items with =IF(COUNTIF(list_b, A2)=0, "Missing in B", "OK"), and run the mirror check from B against A.
Why do identical-looking items show as mismatched?
Case, extra spaces, or leading zeros differ. Build a normalized key on both lists before comparing.
How do I catch matching keys with different amounts?
Compare totals per key: =SUMIF(a_key,key,a_amt) - SUMIF(b_key,key,b_amt).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Flag duplicates · Find values not in a list · Normalize match key

Function references: COUNTIFISNA