Which customers are on last month’s list but not this month’s? Which invoice numbers in the bank export are missing from your records? Comparing two lists is one of the most common Excel jobs. Here are five easy ways to do it.
Example: List A (A2:A100) = customers in September. List B (B2:B100) = customers in October.
1. Highlight items that appear in both lists (fastest)
- Select both columns (hold Ctrl to select A2:A100 and B2:B100).
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Choose Duplicate to color matches, or Unique to color items found in only one list.
It’s quick and visual, but it also flags duplicates within the same list.
2. Mark each item as “Found” or “Missing” with COUNTIF
In C2, next to List A:
=IF(COUNTIF($B$2:$B$100, A2)>0, "Found", "Missing")Copy down. “Missing” means the customer was in September but not in October. Swap the ranges to check the other direction. Learn more in SUMIFS and COUNTIFS.
3. List only the missing items with FILTER
=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0, "None missing")Returns a clean list of everything in A that’s not in B (Excel 365/2021+). See UNIQUE, SORT and FILTER.
4. Bring back details with XLOOKUP
If List B has more columns (like amount or date), pull them across and show “Not found” when missing:
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")See XLOOKUP explained. Older Excel: =IFERROR(VLOOKUP(A2,Sheet2!A:C,3,FALSE),"Not found").
5. Compare row by row
When two columns should match line by line (e.g. before and after an update):
=IF(A2=B2, "Same", "Different")This ignores upper/lower case. For an exact, case-sensitive check, use =EXACT(A2,B2). See case-sensitive lookups.
Highlight differences: select both columns → Conditional Formatting → New Rule → “Use a formula” → =$A2<>$B2 → choose a fill color.
Common reasons matches fail
- Extra spaces or hidden characters: clean with TRIM and CLEAN.
- Numbers stored as text in one list: see numbers stored as text.
- Different formats: “INV-0042” vs “INV-42”, or dates vs text dates.
- Typos: “Al Noor Trading” vs “Al-Noor Trading.”
Which method should you use?
| Goal | Method |
|---|---|
| Quick visual check | Conditional formatting → Duplicate Values |
| Label each item | COUNTIF → Found / Missing |
| A list of what’s missing | FILTER + COUNTIF |
| Bring back details | XLOOKUP |
| Same row, same value? | A2=B2 or EXACT |
Google Sheets
COUNTIF, XLOOKUP and conditional formatting work the same. FILTER uses this syntax: =FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0).