How to Compare Two Lists in Excel and Find What Is Missing

Comparing two lists is the job behind most reconciliations: which customers are not in the new system, which orders were never paid, which emails are on the unsubscribe list. Excel can do it in a minute, provided the lists are clean.

Step 0: clean both lists first

"John.Smith@Example.com " and "john.smith@example.com" are the same person and different text. Before comparing, trim and lowercase both columns:

=LOWER(TRIM(A2))

See removing extra spaces if a comparison still fails on items that look identical.

Method 1: COUNTIF (works in every version)

Next to list A (column A), in B2, with list B in column D:

=IF(COUNTIF($D$2:$D$5000,A2)=0,"Missing from B","In both")

Fill it down. To find the opposite (items in B that are not in A), put a mirror formula beside list B.

Method 2: XLOOKUP or MATCH

In Microsoft 365 or Excel 2021:

=IF(ISNA(XLOOKUP(A2,$D:$D,$D:$D)),"Missing from B","In both")

In older versions use MATCH:

=IF(ISNA(MATCH(A2,$D:$D,0)),"Missing from B","In both")

MATCH with 0 as the last argument means "exact match", which is what you want.

Method 3: highlight without formulas

Select both lists, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Choose "Unique" instead of "Duplicate" to colour only the items that appear once, in other words the differences.

Comparing two sheets

Use the same formulas and refer to the other sheet: =COUNTIF(Sheet2!$A$2:$A$5000,A2). If the comparison needs several columns to match (for example first name, last name and date of birth), add a helper column that joins them: =LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))&"|"&C2, then compare the helper columns.

Why a comparison says "missing" when the item is there

Exact matches are not always enough

Lists from two systems rarely match perfectly: one has "St." where the other has "Street", one has "Jon" where the other has "John". Formulas only find exact equality. A cleaning pass that normalises dates, phone numbers, emails and spacing first makes far more items match.

Questions people ask