Compare Two Lists in Excel

Say List 1 is in column A (A2:A100) and List 2 is in column B (B2:B100). Below are reliable ways to find matches and differences, plus a no-formula online option. This page does not upload a workbook. Copy one column at a time.

1. Highlight duplicates with conditional formatting

Select both columns.
Go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
Choose Duplicate to color values that appear more than once.

This is a quick visual check only. It also flags values repeated inside one column, and Unique misses an item that appears twice in List 1 but never in List 2. For a true only-in-one-list answer, use the COUNTIF method below.

2. COUNTIF: is each item in the other list?

In C2 type =IF(COUNTIF($B$2:$B$100,A2)>0,"In both","Only in List 1") and fill down. Swap the ranges to check List 2 against List 1. COUNTIF is not case-sensitive.

3. MATCH or XLOOKUP

=IF(ISNA(MATCH(A2,$B$2:$B$100,0)),"Missing","Found") works in every Excel version. In Excel for Microsoft 365 and Excel 2021 or later you can use =XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Missing").

4. Compare two columns row by row

To check whether the same row matches, use =IF(A2=B2,"Match","Different"), or =EXACT(A2,B2) for a case-sensitive check. Row-by-row comparison is a different job from comparing two sets of values.

Google Sheets

COUNTIF, MATCH, and EXACT work the same way in Google Sheets. For conditional formatting, use Format, Conditional formatting, Custom formula is, with =COUNTIF($B:$B,A1)>0.

Common reasons matches fail

Trailing spaces: wrap values in TRIM().
Numbers stored as text in one column.
Non-breaking spaces copied from web pages. TRIM does not remove every hidden character.

No formulas needed

Copy each column and paste the columns into the compare two lists tool to get only in A, only in B, and in both at once. Use find duplicates when you only want the overlap, or remove duplicates when you have a single list.

Frequently asked questions

How do I compare two lists in Excel for differences?

Use =IF(COUNTIF($B$2:$B$100,A2)=0,"Only in List 1","") next to List 1, then do the reverse for List 2. Conditional formatting is only a quick visual check, because repeats inside one list confuse it.

Is COUNTIF case-sensitive?

No. Use EXACT for a case-sensitive comparison.