How to Compare Two Lists in Google Sheets (and a Faster Way)
Google Sheets has no single built-in "compare two lists" feature, but a handful of formulas and conditional formatting rules get you most of the way there. Each approach has a specific use case and a specific gap. Here's how to set up the common ones, what breaks them, and a route that skips formulas entirely.
Quick answer
COUNTIF(B:B, A2) flags whether each A value exists in B; MATCH wrapped in IFNA does the same with a cleaner Not found result; conditional formatting with a custom COUNTIF formula highlights unmatched cells directly on the sheet. All three are case-insensitive by default and break on stray spaces, mismatched text-vs-number formatting, or hidden characters. Pasting both columns into the Compare 2 Lists tool skips the formula step and returns A-only, B-only, intersection, and union results immediately, with explicit options for case sensitivity and whitespace.
Skip the formulas - compare two lists instantly.
Open Compare 2 Lists →Method 1: COUNTIF as a flag column
The most common approach. In a helper column next to List A, enter:
=COUNTIF(B:B, A2)>0
This returns TRUE if the value in A2 appears anywhere in column B, and FALSE otherwise. Copy the formula down the column and you get a quick flag for every row. It's fast to set up but purely a yes/no signal - it doesn't tell you where the match is or handle any normalization on its own.
Method 2: MATCH for existence, VLOOKUP for the matched value
=IFNA(MATCH(A2, B:B, 0), "Not found") returns the row number where A2
was found in B, or the text "Not found" if it wasn't - useful when you want a readable label
rather than TRUE/FALSE. If you need to actually pull back a corresponding value from a second
column in B rather than just confirming existence, use =IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not found"), which looks up A2
in the first column of range B:C and returns the value from the second column.
Method 3: Conditional formatting to highlight differences directly
Select the range you want to check (say, column A), open Format → Conditional formatting, choose Custom formula is, and enter:
=COUNTIF(B:B,A1)=0
This highlights every cell in A that has no match anywhere in B, directly on the sheet - no helper column needed. Set up a mirrored rule on column B (swapping A and B in the formula) to highlight B-only values as well.
The pitfalls that trip up all three methods
- Case sensitivity: COUNTIF and MATCH are both case-insensitive by default, so "Apple" and "apple" are treated as the same value - which may or may not be what you want.
- Stray whitespace: a trailing space copied in from a web page or another spreadsheet makes two visually identical values fail to match, since none of these formulas trim automatically.
- Numbers stored as text: a value entered or imported as text (often left-aligned, sometimes with a small warning triangle) won't match the same number stored as an actual numeric type, even though they display identically.
- Partial ranges: forgetting to lock a range reference (using B2:B100 instead of B:B, without $ anchors) means the comparison range shifts unexpectedly when the formula is copied down or across.
The same traps catch you when cleaning a single column. If Data cleanup → Remove duplicates leaves copies behind, stray spaces or numbers stored as text are almost always the reason. The guide to removing duplicates in Google Sheets covers that menu, the UNIQUE() formula, and how to flag repeats with COUNTIF first.
A faster route when you just need the answer
If the goal is simply "what's in A but not B, what's in both, what's the combined list" rather than building a permanent formula into the sheet, it's often quicker to copy the two columns out of Sheets entirely. Paste List A and List B into the Compare 2 Lists tool and it immediately shows A Only, B Only, the intersection, and the union - each with its own count and copy button. Case sensitivity, leading zero handling, and whitespace trimming are all built-in toggles rather than something you have to remember to write into the formula yourself.
Working with Excel instead of Sheets?
The same comparison approach works for two Excel columns too.