How to Match Two Lists in Excel and Find What's Missing

To find what's in one list but not the other, ask Excel how many times each item appears in the other list. Zero means missing.

=IF(COUNTIF($D$2:$D$14, $A2)=0, "Missing", "Found")

Copy it down beside your first list and you have your answer. The rest of this guide covers the part that catches people out — running the check in both directions, and the three reasons a match you can see with your own eyes still comes back as missing.

The same two lists, checked in both directions. Each side finds exceptions the other cannot see.

Why COUNTIF and not VLOOKUP

VLOOKUP answers "what is the value next to this?" — and returns #N/A when there isn't one. You then have to wrap it in IFERROR and treat the error as your answer, which is a roundabout way of asking a yes-or-no question.

COUNTIF answers "how many times does this appear?" That's the question you actually have. It returns a number, never an error, and the number is useful in its own right — a 2 tells you something a "Found" would have hidden.

Result	What it means
0	Not in the other list
1	In the other list once — the normal case
2 or more	In the other list more than once, which is usually its own problem

If you only ever want the count, drop the IF entirely and read the numbers. COUNTIF is covered in full here.

Both directions, always

This is the mistake that matters. Checking list A against list B finds items you have that they don't. It says nothing about items they have that you don't.

So you write a second formula, pointing the other way:

=IF(COUNTIF($A$2:$A$15, $D2)=0, "Not in system", "Found")

In the example above, one direction finds two invoices; the other finds a different one entirely. Someone who ran only the first check would have called the reconciliation finished with a payment still unaccounted for.

⚠️ Watch the ranges. The first formula's range is the other list, locked with $ so it doesn't slide as you copy down. The cell being tested is the only part that should move. Getting these the wrong way round produces a column of Found that means nothing — every item is trivially present in its own list.

Three reasons a visible match reports as missing

Every so often COUNTIF insists an item isn't there when you can see it on screen. It's telling the truth; the two values differ in a way the display doesn't show.

1. Trailing spaces

The most common cause by a distance. "INV-1003 " and "INV-1003" are different strings, and one came out of a system that padded it.

=COUNTIF($D$2:$D$14, TRIM($A2))

To confirm that's the problem before you fix it, check the lengths:

=LEN(A2)

If one list gives 8 and the other 9 for what looks like the same code, you've found it. TRIM strips leading and trailing spaces and collapses runs of them inside the text.

Note that TRIM here fixes only one side. If the padding is in the other list, wrap that range instead — or better, clean both lists once with a helper column rather than nesting TRIM into every formula that touches them.

2. Numbers stored as text

One list was typed and holds real numbers. The other was imported and holds text that looks like numbers. 1001 and "1001" do not match.

The tell: text drifts to the left of its cell, numbers sit right. Select the column and check the status bar — if Sum shows nothing for a column of apparent numbers, they're text.

Fix the imported column with Data → Text to Columns → Finish, which sounds like it does nothing and in fact re-parses every cell in place. Or convert in a helper column:

=VALUE(A2)

3. Asterisks and question marks in your codes

This one is genuinely obscure, and it produces the worst kind of error: a wrong answer that looks right.

COUNTIF treats * as "any number of characters" and ? as "any single character". If your reference numbers contain either — some accounting systems use them as separators — COUNTIF stops comparing text and starts pattern matching. A code of AB*100 will happily match AB99100, and report a match that doesn't exist.

The fix is to compare with EXACT instead, which does no pattern matching at all:

=IF(SUMPRODUCT(--EXACT($D$2:$D$14, $A2))=0, "Missing", "Found")

EXACT compares each cell in the range against your value and returns an array of TRUE and FALSE. The double minus converts those to 1 and 0, and SUMPRODUCT adds them — so the result is again a count, and again zero means missing.

This version has a second benefit worth knowing about: EXACT is case sensitive, where COUNTIF is not. If abc-100 and ABC-100 are different things in your data, COUNTIF will merge them and this formula won't.

Showing only what's missing

Once the status column exists, there are two ways to act on it.

Filter — click the header, filter to Missing, and you have your list. Copy it straight into an email. This is right when you want to hand the exceptions to someone.

Colour — a conditional formatting rule on the whole row, so exceptions stand out while you keep working in the sheet:

=COUNTIF($D$2:$D$14, $A2)=0

Select the data, Conditional Formatting → New Rule → Use a formula, paste that, pick a red fill. Note the mixed anchoring: $A2 locks the column so the whole row colours from one test, while the row number stays free to move down. That anchoring is the single most common reason a conditional formatting rule colours the wrong cells.

Does this work in Google Sheets?

Yes — COUNTIF, TRIM, LEN, VALUE, EXACT and SUMPRODUCT all behave the same, and the wildcard trap is identical.

Sheets does have a shortcut Excel lacks. Both of these return the missing items as a list, no status column and no filtering:

=FILTER(A2:A15, COUNTIF(D2:D14, A2:A15)=0)

Excel has FILTER too, but only in Microsoft 365 and Excel 2021 onwards. The COUNTIF method works in every version, which is why it's the one taught here.

When it goes wrong

Every row says Found

The range points at the same list as the cell being tested. Every item matches itself.

The answers shift as you copy down

The range isn't locked. It needs $D$2:$D$14, not D2:D14. Select the range in the formula bar and press F4.

Some rows say Found and some say #VALUE!

Usually a VALUE() wrapper meeting a cell that isn't a number at all — a blank, or a code with a letter in it. Wrap it: =IFERROR(VALUE(A2), A2).

Two lists of the same length, and everything reports missing both ways

Almost certainly the number-stored-as-text problem, in whichever list came from an export.

Common questions

How do I find items in both lists rather than in neither?

Change the test to >0. The same formula answers both questions depending on which side of zero you ask about.

Can I compare lists on different sheets?

Yes. Prefix the range with the sheet name: COUNTIF(Bank!$D$2:$D$14, $A2). Different workbooks work too, but the formula breaks when the other file is closed, so copy the data into one workbook first.

What if I need to match on two columns at once?

Name and date, for instance. Use COUNTIFS with both conditions rather than joining the values into one string — joining creates its own false matches when the boundary between the two values is ambiguous.

My lists are thousands of rows and it's slow

COUNTIF against a large range is genuinely expensive, because it scans the whole range for every row. Sort both lists first if you can, or convert the finished status column to values with Paste Special → Values once you've read it.

Should I delete the rows that don't match?

Not until you know why they don't. A mismatch is usually a data problem rather than a missing record, and deleting is how a formatting difference turns into lost data.

The short version

• =COUNTIF(other list, this cell)=0 is the whole technique.
• Lock the range with $; leave the tested cell free to move.
• Run it in both directions, or you'll find half the problem.
• Trailing spaces first, numbers-as-text second — those are most mismatches.
• If your codes contain * or ?, use SUMPRODUCT(--EXACT(...)) instead.
• EXACT is also the answer when case matters.

Next steps: the counting behind this is COUNTIF and COUNTIFS, and if the two lists turn out to have repeats inside them as well as between them, removing duplicates safely comes first. When you need the value beside the match rather than a yes or no, that's INDEX MATCH.