Comparing data
How to Compare Two Lists in Excel and Find What Is Missing
To find items in list A that are missing from list B, put =IF(COUNTIF($B$2:$B$5000,A2)=0,"Missing","Found") next to list A and fill it down. In Microsoft 365 you can use =IF(ISNA(XLOOKUP(A2,B:B,B:B)),"Missing","Found"). Clean both lists first (spaces and capitals), or equal items will look different.
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
| Reason | How to check | Fix |
|---|---|---|
| Hidden spaces or non-breaking spaces | =LEN(A2) is longer than it looks | Clean both lists |
| Numbers stored as text in one list | One side is left-aligned | Convert text to numbers |
| Different spelling or typos | The items differ by a letter | Fix the typos, or use a fuzzy tool |
| Different capitals | COUNTIF ignores case, but exact formulas such as EXACT do not | Lowercase both |
| Leading zeros lost | 00123 against 123 | Keep IDs as text in both |
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.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
How do I get a list of only the missing items?
Filter the result column for "Missing", then copy the visible rows. In Microsoft 365, =FILTER(A2:A5000,COUNTIF(D2:D5000,A2:A5000)=0) returns them directly.
Is there a limit to how many rows I can compare?
Excel handles hundreds of thousands of rows, but COUNTIF over a very large range for each row gets slow. Sorting both lists and using MATCH with a sorted lookup, or Power Query's Merge feature, is much faster.
Can I compare lists without formulas?
Yes: Power Query's Merge, with join kind "Left Anti", returns exactly the rows in one list that are not in the other.