All Tools VerseTo compare two lists, you want one of three answers: what is in the first list but not the second, what is in both, or everything that differs. Below are three ways to get those answers: a free online tool for a quick check, formulas for Excel, and formulas for Google Sheets. Each method uses the same two small lists, so you can check your result against ours.
List A: apple, banana, cherry, date
List B: banana, date, elderberry, fig
In this guide
Compare two lists online
The fastest way for a one-off check is the free Compare Two Lists tool. Paste List A and List B, one item per line, and pick what you want to see:
| You want | Mode | Result with our lists |
|---|---|---|
| Items only in List A (what is missing from B) | A − B | apple, cherry |
| Items only in List B (what is new) | B − A | elderberry, fig |
| Items in both lists (matches) | A ∩ B | banana, date |
| Everything that differs | A ⊕ B | apple, cherry, elderberry, fig |
| One merged list without duplicates | A ∪ B | apple, banana, cherry, date, elderberry, fig |
The tool trims extra spaces by default and can ignore upper and lower case, which removes the two most common reasons two lists “should” match but don’t. Both lists stay in your browser.
Compare two lists in Excel
Put List A in column A and List B in column B, with headers in row 1, so the items start in row 2.
- Mark every item in A as a match or not. In C2 enter
=IF(COUNTIF($B$2:$B$1000,A2)>0,"In both","Only in A")and fill down. Swap the columns to check List B against List A. - Get TRUE or FALSE instead.
=ISNUMBER(MATCH(A2,$B$2:$B$1000,0))returns TRUE when the item is also in column B. - List only the missing items (Excel 365 and Excel 2021):
=FILTER(A2:A1000,(COUNTIF(B2:B1000,A2:A1000)=0)*(A2:A1000<>"")). With our lists it returns apple and cherry. - List only the matches:
=FILTER(A2:A1000,COUNTIF(B2:B1000,A2:A1000)>0)returns banana and date. - Highlight the differences. Select column A, then Home > Conditional Formatting > New Rule > Use a formula, and enter
=COUNTIF($B:$B,$A1)=0. Items that are not in column B get the color you choose.
For a quick visual check you can also select both columns and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Items that appear in both columns light up, but repeats inside one column light up too.
Compare two lists in Google Sheets
The Excel formulas above work in Google Sheets as they are, including FILTER. Two extras are useful in Sheets:
- Missing items without duplicates:
=UNIQUE(FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)=0)) - Highlight differences: select column A, open Format > Conditional formatting, choose “Custom formula is” and enter
=COUNTIF($B:$B,$A1)=0.
Why matches get missed
- Extra spaces. “apple ” with a trailing space is not “apple” to a formula. Clean the column with
=TRIM(A2)first. - Numbers stored as text. 1024 typed as a number and “1024” imported as text do not match with
MATCH. Convert one column so both have the same type. - Upper and lower case.
COUNTIFandMATCHignore case, so “Apple” matches “apple”. If case matters, use=SUMPRODUCT(--EXACT(A2,$B$2:$B$1000))>0. - Duplicates inside one list. If an item appears twice in A, it is reported twice. Remove repeats first with the duplicate remover, or see how to remove duplicates from a list.
More text guides and tools: free text tools.
Frequently asked questions
How do I find the differences between two lists?
You need the items that are in one list but not the other. In the online tool choose A ⊕ B. In Excel or Google Sheets, run the “missing items” FILTER formula twice, once in each direction.
How do I compare two lists for matches in Excel?
Next to each item in column A enter =COUNTIF($B$2:$B$1000,A2)>0. TRUE means the item is also in column B. In Excel 365, =FILTER(A2:A1000,COUNTIF(B2:B1000,A2:A1000)>0) lists all the matches at once.
Can I compare lists that use commas instead of new lines?
Yes. In the online tool, set the input separator to comma. In a spreadsheet, split the text first with Data > Text to Columns (Excel) or Data > Split text to columns (Google Sheets).
Does the order of the items matter?
No. All the methods above compare values, not positions. If you need the result in order, sort it with the list alphabetizer.