How to Compare Two Lists in Excel
Compare two Excel lists with COUNTIF, XLOOKUP, conditional formatting, or Power Query to find matches and missing values accurately.
Method 1: compare membership with COUNTIF
When order does not matter and you only need to know whether an item exists, COUNTIF is direct. If List 1 is in A2:A100 and List 2 is in B2:B100, enter =COUNTIF($B$2:$B$100,A2)>0 in C2 and fill down. TRUE means A2 appears at least once in List 2. Wrap it in IF when labels such as Match and Missing are easier to filter.
Lock the lookup range with dollar signs so it does not slide while the formula is copied. This method treats repeated values as present but does not prove that both lists contain the same number of copies. If quantity matters, compare =COUNTIF($A$2:$A$100,A2) with =COUNTIF($B$2:$B$100,A2) on a unique key list.
Method 2: return related details with XLOOKUP
Use XLOOKUP when a match should return a status, amount, or description. For example, =XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"Missing") searches D for A2 and returns the aligned value from E. Exact match is the default, and the explicit not-found value makes missing keys visible without hiding other errors. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
Older workbooks can use =IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Missing"). VLOOKUP requires the key to be in the first column of its table, while XLOOKUP can return from either side. In both cases, confirm that the key has one intended row; otherwise the first match may conceal duplicate source records.
Method 3: highlight or merge the differences
For a visual review, select the first list and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use =COUNTIF($B$2:$B$100,A2)=0 to format items missing from List 2. Create the reverse rule on List 2 to see omissions in both directions, and check the Applies to range before accepting the rule.
For recurring comparisons of large exports, Power Query is easier to audit. Load each range as a table, merge queries on the key, and choose a Left Anti join to return rows found only in the first table. Repeat in the other direction for a full difference report, and inspect duplicate keys before joining because they can multiply rows.
Where the comparison goes wrong
A row-by-row formula such as =A2=B2 compares positions, not list membership; one inserted item makes every following row look different. Text keys can also fail because of invisible spaces, so test known matches and known misses.
Types behave differently by function. XLOOKUP, MATCH, and VLOOKUP treat the number 100 and the text "100" as different values, so a visible match can return #N/A. COUNTIF is more forgiving: it coerces the criterion, so it can report a match that XLOOKUP would miss. COUNTIF also reads *, ?, and ~ inside a key as wildcard characters, so a key such as "P*" matches every code that starts with P. Compare both directions before declaring the lists identical.
Your turn
In C2, count whether the SKU in A2 appears in B2:B5.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | Matches |
| 2 | P-20 | P-10 | |
| 3 | P-30 | P-20 | |
| 4 | P-50 | P-40 | |
| 5 | P-50 |
Answer: =COUNTIF(B2:B5,A2)
Worked example
List 1 is A2:A4, List 2 is C2:C5. “Match” only says the item exists; the count shows List 2 holds two copies of Pens.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | |
| 2 | Pens | Ink | |
| 3 | Ink | Glue | |
| 4 | Tape | Pens | |
| 5 | Pens |
| Formula | Result |
|---|---|
=COUNTIF($C$2:$C$5,A2)>0 | TRUE |
=IF(COUNTIF($C$2:$C$5,A4)>0,"Match","Missing") | "Missing" |
=COUNTIF($C$2:$C$5,A2) | 2 |
=IFNA(VLOOKUP(A3,$C$2:$C$5,1,FALSE),"Missing") | "Ink" |
=COUNTIF($C$2:$C$5,"P*") | 2 |
Quick mistake checklist
- Comparing row positions when the lists are sorted differently.
- Checking only A against B and not B against A.
- Ignoring duplicate counts or mismatched text and number types.
Frequently asked questions
How do I find values in A that are not in B?
Use =COUNTIF($B$2:$B$100,A2)=0 and fill down, then filter for TRUE.
Can I compare lists in different sheets?
Yes. Qualify the range, for example =COUNTIF(Sheet2!$A$2:$A$100,A2)>0.
Why does a visible match return false?
With XLOOKUP, MATCH, or VLOOKUP, one value may be text while the other is numeric, or the text may contain extra spaces or hidden characters.
What is best for a monthly repeat process?
Power Query is often best because a documented merge can be refreshed with replacement source tables.
Practice the skill
Continue with a checked FormulaCamp lesson: XLOOKUP Free
Open the free lesson