How to compare two lists in Excel

One formula answers both questions: COUNTIF counts how often an item of list A appears in list B. Zero means missing, anything else means it is in both.

1. The formulas

List A in column A, list B in column B, both with a header in row 1. In C2 and D2:

=COUNTIF($B$2:$B$5,A2)=0     (TRUE = missing from list B)
=COUNTIF($B$2:$B$5,A2)>0     (TRUE = in both lists)

Fill both down to the last row of list A, and widen $B$2:$B$5 to your list (or use B:B). The $ signs keep the range fixed while the formula is copied.

2. Result on a small example

List A   List B   Missing from B?   In both?
apple    pear     FALSE             TRUE
kiwi     Apple    TRUE              FALSE
plum     fig      TRUE              FALSE
fig      grape    FALSE             TRUE

Note row 2: apple counts as present because list B has Apple. COUNTIF ignores case. For a case-sensitive check use =SUMPRODUCT(--EXACT($B$2:$B$5,A2))=0.

3. Filter or highlight

Filter: Data → Filter, then show only TRUE in column C to get the items missing from B.
Highlight: select A2:A5, Home → Conditional Formatting → New Rule → Use a formula, enter =COUNTIF($B$2:$B$5,A2)=0 and pick a fill color.
For the other direction (items of B missing from A), swap the columns in the formula: =COUNTIF($A$2:$A$5,B2)=0.

4. Common snags

Trailing spaces make "apple " and "apple" different. Clean a column with =TRIM(A2) first.
Numbers stored as text (left-aligned, green triangle) do not match real numbers. Convert them with Data → Text to Columns → Finish.

5. No formulas

Copy each column and paste them into the free compare two lists tool: it shows only-in-A, only-in-B and in-both at once, ignores case and spaces if you want, and removes duplicates. Nothing is uploaded.

Frequently asked questions

Is COUNTIF case-sensitive?

No. 'Apple' and 'apple' match. Use SUMPRODUCT with EXACT, as above, when case matters.

Can I use VLOOKUP or XLOOKUP instead?

Yes: =ISNA(VLOOKUP(A2,$B$2:$B$5,1,FALSE)) is TRUE for missing items. COUNTIF is shorter and does not need an error check.

Written with AI assistance. The formulas were evaluated on 2026-10-01 in LibreOffice Calc (same COUNTIF semantics as Excel) on the example below; results as shown. PlainTables is our extension; the other methods are free and built into Excel, Google Sheets and pandas.