How to compare two lists in Excel and find what's missing

Say column A has 50 phone numbers and column B has 100, and you need to know which numbers from A are not in B. Put this in C2 and fill it down:

=IF(ISNA(XMATCH(A2, B:B)), "not in B", "")

Then do the same in the other direction (in D2, next to list B) to find what is in B but not in A:

=IF(ISNA(XMATCH(B2, A:A)), "not in A", "")

That is the whole idea: look up each value in the other list, in both directions. People often check only one direction and miss half of the differences. The rest of this guide covers older Excel versions, getting the result as a clean list, highlighting, and the traps that make a value look missing when it is not.

1. One formula per row (any Excel version)

XMATCH needs Microsoft 365 or Excel 2021 and later. In older versions, and in most tutorials, you will see COUNTIF or MATCH:

=IF(COUNTIF(B:B, A2)=0, "not in B", "")
=IF(ISNA(MATCH(A2, B:B, 0)), "not in B", "")

Both work for everyday lists, but they have quirks that XMATCH does not:

• Wildcards. COUNTIF, and MATCH with a text value, treat * and ? as wildcards. A product code like AB*1 "matches" AB-XL-1, so a missing value looks present.
• Long numbers. COUNTIF does not correctly count numbers longer than 15 digits (Exceljet), so two different card or account numbers stored as text can count as the same.

XMATCH does an exact match by default and only uses wildcards if you ask for them, which is why it is the better choice when you have it.

To mark values that are in both lists instead, flip the test:

=IF(ISNUMBER(XMATCH(A2, B:B)), "in both", "")

2. Get the missing values as a list (Microsoft 365, Excel 2021+)

Instead of a helper column you can scroll through, let one formula spill the result. Adjust the ranges to your data:

=FILTER(A2:A51, ISNA(XMATCH(A2:A51, B2:B101)), "nothing missing")

This returns every value in A that is not in B. Swap the ranges for the other direction. To list the values in both lists:

=FILTER(A2:A51, ISNUMBER(XMATCH(A2:A51, B2:B101)))

In Google Sheets the same idea works with COUNTIF:

=FILTER(A2:A51, COUNTIF(B2:B101, A2:A51)=0)

3. Highlight the differences

Quick way: select both columns, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values and switch the drop-down from Duplicate to Unique. Values that appear only once are coloured. This works only if neither list has duplicates of its own, because a value repeated inside list A also counts as a "duplicate".

Reliable way: select A2:A51, choose Conditional Formatting → New Rule → Use a formula, and enter:

=COUNTIF($B$2:$B$101, A2)=0

Repeat for column B with the references swapped. The $ signs keep the other list fixed while the rule moves down your column.

4. Why values that should match don't

If the formulas report a value as missing but you can see it in the other list, it is almost always one of these:

• Numbers vs text. 12345 stored as a number and '12345 stored as text never match with XMATCH or MATCH. A green triangle in the cell corner is the hint. Convert one list (Data → Text to Columns → Finish is a quick way to turn text into numbers), or compare both as text: XMATCH(A2&"", $B$2:$B$101&"") (use a fixed range, not the whole column, or it gets slow).
• Lost leading zeros. 0511234567 becomes 511234567 when a file is opened and the column is treated as a number. Format the column as Text before pasting or importing.
• Invisible spaces. A trailing space or a non-breaking space (common in data copied from web pages) makes "Acme" and "Acme " different. TRIM() removes normal spaces but not non-breaking ones; use TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
• Letter case. All of the formulas above ignore case. If abc and ABC must count as different, use =SUMPRODUCT(--EXACT(A2, $B$2:$B$101))=0.

5. Long lists, or lists you would rather not paste into a spreadsheet

With hundreds of thousands of values, whole-column lookups make Excel slow, and a sheet stops at 1,048,576 rows. If the lists come from two exports anyway, you can skip the formulas:

1. Save each list as a CSV file with a header in the first row, for example phone (in Excel: File → Save As → CSV UTF-8).
2. Open Calamo's free compare tool and drop the old list as file A and the new list as file B. With a single column, it is picked as the key automatically.
3. "Rows removed from A" are the values only in list A, "rows added in B" are the values only in list B, and "unchanged" counts the values in both. You can download each group as a CSV.

Leading zeros are kept because values are read as text, trailing spaces are ignored, and values that appear twice in one list are reported instead of silently skewing the result. The files are read in your browser and never uploaded.

Comparing whole rows, not just one column – for example two customer exports where an address or price might have changed? See how to compare two Excel sheets for differences.

Questions