How to compare two lists in Excel and instantly spot what doesn't match

You have expected payments in one column and actual bank deposits in another. Eyeballing them works until there are four hundred rows — and then it stops working in a way you won't notice until month-end. Here's the version that takes a minute and actually ties out.

The formula

In a helper column next to your first list:

=COUNTIF($B$2:$B$500, A2)

It counts how many times the value in A2 appears in list B. 0 means it's missing. 1 or more means it matched. Fill the formula down the whole column. (The $ signs lock the B range so it doesn't drift as you fill down.)

Make the misses obvious

Select your helper column.
Home → Conditional Formatting → Highlight Cell Rules → Equal To.
Enter 0 and pick a red fill.

Every unmatched row now lights up. No scrolling, no squinting.

Check the other direction too

This is where most people stop — and it's exactly why reconciliations still don't tie. Running it one way only tells you what's in A but missing from B. Repeat the formula on the B side, pointing back at A, to catch the rows that exist in your bank feed but never made it into your expected list. Both directions, every time. A value missing from either side is a break you need to explain.

Four things that fake a mismatch

Before you chase a "missing" row as a real exception, rule out the four classic false mismatches:

Leading and trailing spaces. A single trailing space makes an identical value fail. Wrap both sides in TRIM/CLEAN first.
Numbers stored as text. Invoice numbers pasted from a PDF are usually text; 1024 and "1024" will never match. Watch for the green corner triangle and convert one side with Text to Columns.
Hidden wildcard characters. COUNTIF treats * and ? as wildcards, so a value containing either can match things it shouldn't. Precede them with a tilde (~*) or switch to an exact method if your data uses them.
Case and punctuation drift. COUNTIF ignores case (which usually helps), but Acme, Inc. and Acme Inc are still two different values. Normalize before you compare.

A cleaner variant for amounts

Matching on dollar amounts? Round both sides first to kill floating-point noise and penny differences you don't care about:

=COUNTIF($B$2:$B$500, ROUND(A2, 2))

…with the B column rounded to two decimals too. For matching on two keys at once (amount and date, say), step up to COUNTIFS.

When it's time to stop doing this by hand

The COUNTIF method is perfect for a one-off or a single account. But when you're reconciling deposits made of several receipts, across multiple entities, every month, a two-column COUNTIF can't untangle which receipts add up to which deposit. That's a different problem — and it's the one TrueBalance was built for: a three-pass matching engine that finds which receipts make up which deposit across entities at once, so the exceptions are the only thing left to review. If that's your month-end, tell us what your reconciliation looks like.