How to compare two lists in Excel with COUNTIF
Two lists that should match, and one of them has gained or lost something. This marks every row from the first list that does not appear in the second.
The formula
=IF(COUNTIF(B:B,A2)=0,A2,"") in column C
=TEXTJOIN(", ",TRUE,C2:C6) in column D
What each part does
COUNTIF(B:B,A2)- Counts how many times this row's value turns up in the other list.
=0- Zero means it is not in the other list at all.
A2- Writes the value itself rather than a word like missing.
TEXTJOIN- Gathers the whole column into one cell, so the answer is a list you can read at a glance.
TRUE- Skips the empty rows, which is what keeps the list clean.
How it works, and what to watch for
The general idea: given two lists that should match, this pulls out everything that is in the first one and missing from the second. COUNTIF counts how many times each value turns up in the other column, and zero means it is not there.
Column C writes the missing value itself rather than a word like "missing", leaving gaps on the rows that matched. TEXTJOIN then gathers that column into one cell, skipping the gaps, so the answer reads as a list rather than a column to scan.
On Microsoft 365 you can do it in one step with =FILTER(A2:A100,COUNTIF(B:B,A2:A100)=0). The two-column version here works in every version of Excel and in Google Sheets.
Swapping the two columns answers the opposite question. Point the count at the first list instead and you get everything new in the second one.
This works on anything paired: invoice numbers against payments received, staff against a sign-in sheet, stock counted against stock recorded, an old mailing list against a new one.
If a value you can clearly see in both lists still gets flagged, it is nearly always a trailing space, or a number stored as text on one side. Wrapping both sides in TRIM clears the first, and matching the types clears the second.
Questions
How do I compare two columns in Excel for differences?
Put this formula beside the first column and fill it down. Every row that does not appear in the other column gets marked, and the unmarked rows are the ones that match.
Can I compare two lists on different sheets?
Yes. Point the count at the other sheet, as in COUNTIF(Sheet2!B:B,A2). Everything else stays the same.
Why is a value flagged when I can see it in both lists?
To Excel they are not identical. Usually one has a space on the end, or one is a number while the other is text. TRIM and matching the types sorts it out.
Will this work in Google Sheets?
Yes. COUNTIF and IF behave the same way there, so the formula can be pasted straight across.
The example sheet
| Last month |
This month |
Missing |
The list |
| INV-1001 |
INV-1001 |
|
|
| INV-1002 |
INV-1004 |
|
|
| INV-1003 |
INV-1002 |
|
|
| INV-1004 |
INV-1006 |
|
|
| INV-1005 |
|
|
|
If this helped, these are close by
More on COUNTIF, and every walkthrough that uses it.
