How to Compare Two Lists in Google Sheets (and a Faster Way)

Guide · Updated June 2026

Method 1: ISNA + MATCH formula

In a blank column next to List A, use:

=IF(ISNA(MATCH(A2, B:B, 0)), "Not in B", "Match")

This flags every A2 value that has no match anywhere in column B. Copy the formula down the column, then use Data → Create a filterto isolate the “Not in B” rows. Swap the ranges to check the reverse direction.

Method 2: Conditional formatting

Select both ranges, open Format → Conditional formatting, and use a custom formula like =COUNTIF($B$:$B, A1)=0 to highlight cells in A with no match in B. Good for a quick visual pass over a small sheet; harder to read once a list runs into the thousands of rows.

A faster way: paste both columns into a comparison tool

Select column A, copy, and paste into List 1; do the same for column B into List 2. You immediately get everything only in A, only in B, what’s shared, and the full combined set — no helper columns, no filters to remember to remove afterward. Toggle case sensitive if your sheet uses codes where case matters, and natural sort if you want numeric IDs ordered correctly instead of alphabetically.

Which is better?

If the comparison needs to live inside the spreadsheet — recalculating as the sheet updates — formulas are the right call. For a one-off “what changed between these two exports” question, pasting into a dedicated comparison tool is faster and easier to read.