Compare Two Lists in Excel: What's Missing, What's New, and What Changed

Two exports of the same thing, a month apart. Your system's list and the supplier's. What you invoiced and what they paid. The question feels like one question — what's different? — but it's really four, and every native method in Excel answers exactly one of them and leaves you to run the next three yourself.

The four questions, and why you need all of them

Hold two lists up against each other and there are only four interesting outcomes. Everyone thinks of the first two. The fourth is where reconciliations go wrong, and the third is the one almost nobody thinks to ask.

That third row is worth dwelling on. Everyone asks "what's missing from my list". Almost nobody asks the reverse — and 27 payments that don't match an invoice is usually real money, sitting in a column no one ran a formula against.

The native answers, one question at a time

COUNTIF — the standard answer, and a good one

=COUNTIF(Sheet2!A:A, A2) returns 0 when the value isn't in the other list and 1 or more when it is. Filter on the zeroes and you have only in your list. Swap the arguments, put it on the other sheet, and you have only in theirs. It's reliable and it's fast.

Two things it can't do. It says nothing about whether the matched rows agree — a SKU present in both lists at wildly different quantities counts as a clean 1. And COUNTIF is loose about types in ways that will surprise you: it treats the text "1001" and the number 1001 as equal, and it does wildcard matching, so a value containing * or ? matches things it shouldn't. COUNTIFS with an exact criteria range, or SUMPRODUCT(--EXACT(...)), is the strict version.

MATCH and ISNA — the same answer, with a diagnosis problem

=ISNA(MATCH(A2, Sheet2!A:A, 0)) is the same test. It has the same blind spot about values, plus the one every lookup has: a miss doesn't say why it missed. A trailing space, a hidden character and a genuinely absent SKU produce identical results. That's a whole problem of its own, covered in why VLOOKUP returns #N/A.

Conditional formatting — fastest to look at, worst to act on

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, applied across both columns, colours everything that appears in both. Thirty seconds, and genuinely useful for eyeballing the shape of the overlap.

Then you need to do something with the result and there's nothing to work with — no column to filter, no count, no list. Colour isn't data. Filtering by colour helps a little, and then you still can't send anyone "the 13 that are missing".

Power Query merge — the real tool, if you'll pay for it

A merge with the right join kind answers three of the four properly: Left Anti gives you only-in-yours, Right Anti gives you only-in-theirs, and Inner gives you the overlap. It's repeatable and it refreshes.

The costs are real though. It's three separate merges to get three of the answers, each one a query. The fourth — do the matched rows agree? — is an inner join plus a custom column comparing the two value columns, plus another to compute the difference, plus a sort. And Power Query merges are case-sensitive and type-sensitive, so "ABC-1" won't join to "abc-1" and the text "1001" won't join to the number 1001 — the exact opposite of COUNTIF's looseness, and the reason a merge that "should work" returns nulls for a third of your rows.

VLOOKUP wrapped in IFERROR — please don't

=IFERROR(VLOOKUP(A2,…),"MISSING") works and is widely recommended. It also silently answers your question from the first matching row when the other list has a duplicate key, and it collapses "no match" and "matched to a blank" into the same output. On a reconciliation, both of those ship as wrong numbers that look right.

All four at once, off one file drop

Compare Two Lists computes every one of the four in a single pass and shows you all four counts before you choose anything. There's no comparison builder to configure — four buttons is the entire feature, which is the point of it.

The key is found by comparing values, not column names

It doesn't assume your two files use the same headers, and it doesn't trust them when they do. It reads the actual values and tells you what it found — 92% of your SKU values appear in its SKU column — and you can override the pick. A header called ID on both sides that holds two unrelated things gets rejected on the evidence.

Turning on one fix moves all four counts

In the file above, three SKUs are stored as text in July and as numbers in June. Before that's dealt with the four answers read 129 / 16 / 18 / 46. Tapping Match as numbers makes them 132 / 13 / 15 / 49 — three rows move out of "only mine" and out of "only theirs" simultaneously, and land in "matched", where three of them turn out to disagree on the numbers. That is one storage quirk quietly corrupting four separate figures, and it's exactly the class of thing a COUNTIF pass never surfaces.

The same restraint as everywhere else applies: the fix changes how the two lists are compared and never writes back to your key column. Your SKUs stay exactly as they arrived.

"The numbers don't agree" — the answer nothing else gives you

It reads money the way a finance person writes it

(1,234.00) is a credit, not a piece of text. A trailing minus (1234-) is what SAP and older ERPs export. A percentage is a number. All of these are read as amounts, which matters more than it sounds: a value that falls through to a text comparison gets reported as a difference with no magnitude — so it adds nothing to the total and sorts to the very bottom of a list ordered by size, which are the two places anyone actually looks.

Text-versus-number is a difference, and it says so

If your side holds the number 4,910.29 and theirs holds the string "4910.29", that is reported as a difference — stored as text → a number — rather than reading as "0 differences" on the one screen whose entire job is showing differences. You may not care. You should get to decide that.

Transposed digits are flagged, never fixed

1240 against 1204. 318.50 against 318.05. When two amounts hold the same digits with exactly two of them swapped, the row is labelled looks like transposed digits. It is stated as a fact and nothing is corrected — deciding which of the two numbers is right is not a thing software should guess at.

The total counts every difference, not the ones on screen

The "+6,340.30 in total" line is accumulated across every differing row. The table below it is capped at a readable length, and an earlier version summed the visible array — which meant the headline discrepancy quietly stopped counting past the cap and understated the real number without saying so. A reconciliation total that silently omits rows is worse than no total at all.

There is no hidden tolerance

Equality is decided on the raw difference. Nothing is rounded first, so there is no accidental half-cent tolerance nobody asked for. A tolerance is available and it is something you switch on deliberately, because "ignore differences under a penny" is a decision about your business, not a default.

A duplicate key is not answered by taking the first row

This is the failure VLOOKUP is famous for and never mentions. If the other list holds your SKU twice with different numbers, "no difference" isn't a true answer — it's the first row's answer. The report says the key is duplicated, says how many of your rows it affects, and marks those rows as disagreeing rather than picking a winner. When the duplicated rows all agree with each other, it stays quiet, because then any of them is safe.

Then choose what happens to your file — including nothing

Four answers, four things you can do with them:

• Add a column — one flag on every row, so you can filter and pivot on it yourself.
• Put them on their own sheet — copied out, with nothing removed from the original.
• Keep only these rows — filter the file down to the answer.
• Just download this list — and this one writes nothing at all. "Send me the unpaid invoices" is a real answer to a real question, and forcing you to mutate your workbook to get it would be a worse product.

Until you pick one, the tool is read-only and says so. Looking at a comparison changes nothing.

What it deliberately doesn't do

• It won't tell you which list is right. It shows you both values and the difference between them. Which one is correct is a question about your business.
• It won't flag rows that aren't in your file. The "only in theirs" answer is real and it's counted, but those rows don't exist in the sheet you're working on, so the only honest thing to offer is the download.
• It won't hide a question it can't answer. If no column holds comparable numbers, or the column you'd compare is empty on one side, the card still appears — with the reason written on it. A missing answer explained beats an answer silently absent.
• It won't invent a near miss on a numbered list. Consecutive SKUs are all one character apart from each other by definition, so on a densely numbered list those aren't offered as typos at all — they're counted in one quiet footnote instead.
• It doesn't upload either file. Both lists are matched in your browser. The second list is a reference: it's read, never changed, and never sent anywhere. See how TidyCell works.

The whole comparison is free and needs no signup — all four counts, every reason, the difference table and the totals, on your own file. Writing the answer into your workbook is a Pro feature.

Common questions

My two lists agree completely. Does it just show an empty table?

No — it says so, plainly: these two lists agree completely, all N rows matched and every number lines up. A comparison that finds nothing is a legitimate and often very welcome result, and it deserves a verdict rather than a blank screen.

What if the two lists use different column names?

Fine. The key is found by comparing values, and you can override the pick on either side. For the value comparison it offers columns whose headers correspond and which hold numbers on both sides, and you can change those too.

Can I compare more than one column of numbers?

Yes — a row counts as disagreeing if any compared column disagrees, and every individual difference is listed and totalled.

One list has dates with times on them and the other doesn't.

The same calendar day is treated as the same day, so a midnight timestamp against a bare date is not reported as a difference.

The second list is in a different file. Or a different tab.

Both work. It offers the other sheets in your workbook first, then any file you attach — and the attached file stays on your computer like everything else.

I want to bring the other list's columns into my file, not just compare.

Then you want Bring Columns Over, which is the same engine pointed at a different job.