Finding what is missing: Excel set differences with COUNTIF and MATCH

"What's in list A but not list B?" is the most common reconciliation question there is — and one spreadsheets actually answer well. The catch: you have to know which formula does what, and where each one quietly lies to you. Here's which to reach for, and the trap hiding in each.

The question behind most reconciliations

Reconciliation is, at its core, set arithmetic. You want three sets: rows in both, rows only in A, and rows only in B. Excel can produce all three from a shared key column. The functions to reach for are COUNTIF and MATCH; conditional formatting makes the result visible.

COUNTIF: is this key present over there?

COUNTIF counts how many times a value appears in a range. Zero means absent.

=IF(COUNTIF(SystemB!$A:$A, A2)=0, "only in A", "in both")

Run it down SystemA, then mirror it on SystemB with the ranges swapped. You now have both one-sided sets. COUNTIF is forgiving and readable, which makes it the right default for presence checks.

MATCH: where is it, and is it there at all?

MATCH returns the position of a value, or an N/A error if absent. Wrap it to get a clean flag:

=IF(ISNA(MATCH(A2, SystemB!$A:$A, 0)), "missing", "found")

Use MATCH when you will reuse the position (for example to pull a value with INDEX). Use COUNTIF when you only care whether it exists — and when you suspect duplicates, because COUNTIF returns a count greater than one where MATCH silently reports only the first hit.

See it: conditional formatting for differences

1. Select the key column on SystemA.
2. New Rule, then Use a formula, then =COUNTIF(SystemB!$A:$A, A1)=0.
3. Set a fill color. Now every row missing from B is highlighted.
4. Repeat on SystemB. Two coloured columns, the whole picture at a glance.

The duplicate trap

Every formula above assumes the key is unique. If a key repeats, COUNTIF returns 2 or 3, MATCH finds only the first, and any value you pull is the value of whichever row happened to come first. Before comparing, check for duplicates: =COUNTIF($A:$A, A2)>1 flags them. A repeating key is not a comparison problem, it is a sign the key is wrong or incomplete — often it needs a second field to become unique.

Mismatch, not just missing

Presence is half the job; the other half is agreement. For keys found on both sides, compare the value with INDEX and MATCH and a rounded difference, as covered in the XLOOKUP guide. The combined output — missing-in-A, missing-in-B, value-mismatch, and matched — is a complete reconciliation, and it is exactly the shape any dedicated tool produces automatically.

← All writing