WebToolNook
— 6 min read

How to Compare Two Lists and Find What's Missing

Short answer: to compare two lists in Excel, put =COUNTIF(B:B, A2)=0 next to list A and fill down. Every TRUE is an item missing from list B. Swap the ranges to go the other way. If you don't want formulas at all, paste both lists into a compare two lists tool and get three results at once: only in A, only in B, and in both.

The formula is the easy part. Most failed comparisons happen because two items that look identical aren't, so the second half of this guide covers why matches fail and how to clean the lists first.

Three questions you can ask of two lists

Be clear on which one you need, because each takes a different formula:

  1. What's in A but not in B? Customers who didn't renew, or files missing from a backup.
  2. What's in both? The overlap between two mailing lists, or students on both registers.
  3. What's different overall? Everything that appears in exactly one of the two lists: A-not-B plus B-not-A.

How to compare two lists in Excel

Assume list A is in column A and list B in column B, both with a header in row 1.

Find items in A that are missing from B (COUNTIF)

In C2, enter:

=COUNTIF(B:B, A2)=0

Fill down. TRUE means A2 does not appear anywhere in column B. Filter column C for TRUE to list the missing items. Change =0 to >0 to find items in both lists instead.

Pull the matching value across (XLOOKUP)

If the lists carry extra columns, for example list B has prices, XLOOKUP finds the match and returns its neighbour: 

=XLOOKUP(A2, B:B, C:C, "Not found")

On Excel 2019 or earlier, which lack XLOOKUP, use =IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not found"). The FALSE matters: it forces an exact match, and leaving it out is the classic VLOOKUP bug.

Highlight the differences (conditional formatting)

Select column A → Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format → enter =COUNTIF($B:$B, A1)=0 → pick a fill colour. Items missing from B light up in place.

Compare two columns row by row

Sometimes the question is "is row 5 in A the same as row 5 in B?", not "does this item appear anywhere?". That needs a different formula: 

=EXACT(A2, B2)

EXACT is case-sensitive. =A2=B2 is not, and treats "Apple" and "apple" as equal.

How to compare two lists in Google Sheets

COUNTIF works the same way. Sheets can also return the whole answer from one cell, with no filling down: 

=FILTER(A2:A, A2:A<>"", COUNTIF(B2:B, A2:A)=0)

That spills every item in A that is missing from B. Swap the A and B ranges for the reverse.

Why identical-looking items don't match

This is where most comparisons quietly go wrong. You can see "Smith" in both lists, but COUNTIF says it's missing. The usual culprits, roughly in order of how often they bite:

  • Trailing spaces. "Smith " is not "Smith", and exports from forms and CRMs are full of them. Fix: =TRIM(A2).
  • Non-breaking spaces. Text copied from a web page often contains character 160 instead of a normal space, and TRIM does not remove it. Fix: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
  • Numbers stored as text. The number 1042 and the text "1042" never match. A small green triangle in the cell corner is the giveaway. Fix: =VALUE(A2), or multiply the column by 1.
  • Lost leading zeros. ZIP codes and product IDs like 00731 become 731 when a CSV is opened in Excel. Compare them as text, or pad them back with =TEXT(A2, "00000").
  • Case. COUNTIF and XLOOKUP ignore case, which is usually what you want. When it isn't, use EXACT.
  • Invisible characters. Line breaks inside cells, tabs, and zero-width spaces from copy-paste. =CLEAN(A2) removes most of them.
  • Duplicates within one list. Not a matching failure exactly, but they inflate the "in both" count. Deduplicate each list first.

A dedicated list-comparison tool handles most of this with switches, which is the main reason to use one even if you know the formulas.

Comparing lists without a spreadsheet

If your lists come from somewhere other than a spreadsheet, such as two exports, an email, a log file, or a column of IDs from a database query, opening Excel just to diff them is overkill.

  1. Paste list A and list B into the compare two lists tool, one item per line.
  2. Leave Trim spaces on. It also converts non-breaking spaces, which fixes the character-160 problem above. Turn on Ignore case if capitalisation doesn't matter.
  3. Copy whichever result you need: Only in A, Only in B, or In both.

It runs in your browser, so a customer list or a set of internal IDs is never uploaded, and duplicate lines are counted once and reported. To clean a single list first, remove duplicate lines or sort it. If you need a line-by-line diff of two versions of the same document, where order matters, use text compare instead.

Which method should you use?

SituationUse
One-off check, lists already in ExcelCOUNTIF
Need a value from the other listXLOOKUP (or VLOOKUP with FALSE)
Want to see the differences in placeConditional formatting
Lists in Google Sheets, one formulaFILTER with COUNTIF
Lists from anywhere, no formulasA compare-two-lists tool
Two versions of one documentA text compare (diff) tool

Try it in your browser

Compare Two Lists Compare two lists to find what is missing, what is extra, and what they share. Trim, ignore case, and copy each result, free and in your browser. Remove Duplicate Lines Remove duplicate lines from text online and keep only unique lines. Options to ignore case, trim spaces, and drop blank lines — instantly, in your browser. Text Compare Compare two texts and highlight every difference, line by line and word by word. Side-by-side or inline diff, private, and nothing is uploaded.

Keep reading