How to Compare Two Lists in Google Sheets (Matches and Differences)
Google Sheets has everything you need to compare two lists: highlight the values that appear in both, flag the ones that are missing, or pull the differences into a separate list that updates automatically. This guide walks through the most useful methods, from a simple highlight to formulas that handle a whole column at once.
All examples assume List A is in column A and List B is in column B, starting in row 2 with headers in row 1. Open-ended ranges such as B2:B include every row below, so new entries are picked up automatically.
Just need a one-off answer? Paste both lists into the free Compare Two Lists tool to see items only in A, only in B and in both – no formulas needed.
1. Highlight matches with conditional formatting
- Select A2:A.
- Go to Format → Conditional formatting.
- Under Format cells if…, choose Custom formula is and enter:
=COUNTIF($B$2:$B,$A2)>0
- Pick a fill color and click Done.
To highlight the values of A that are missing from B instead, use:
=COUNTIF($B$2:$B,$A2)=0
Add a second rule on B2:B with the column letters swapped to color List B as well. The formula is written for the first cell of the range ($A2); Sheets adjusts it for every other row.
2. Flag each item with COUNTIF or MATCH
A helper column gives you a result you can sort and filter. In C2 enter:
=IF(COUNTIF($B$2:$B,A2)>0,"Match","No match")
Or with MATCH, which returns TRUE or FALSE:
=ISNUMBER(MATCH(A2,$B$2:$B,0))
Drag the formula down, then use Data → Create a filter to show only matches or only differences.
3. Check the whole column with one formula (ARRAYFORMULA)
Instead of copying the formula into every row, a single ARRAYFORMULA in C2 fills the whole column and also covers rows you add later:
=ARRAYFORMULA(IF(A2:A="",,IF(COUNTIF(B2:B,A2:A)>0,"Match","No match")))
The IF(A2:A="",,…) part leaves empty rows blank instead of filling them with "No match".
4. Extract matches or differences into a new list (FILTER)
FILTER returns a live list that updates when either list changes. Values of A that are also in B:
=FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)>0)
Values of A that are not in B:
=FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)=0)
The A2:A<>"" condition is important: without it, empty rows of A would be counted as matches against the empty rows of B. To remove duplicates and sort the result, wrap it in UNIQUE and SORT:
=SORT(UNIQUE(FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)=0)))
Swap A and B in the formula to get the values that are only in List B.
5. Look up matching data with XLOOKUP or VLOOKUP
If List B has related data – for example prices in column C – XLOOKUP returns it for every match:
=XLOOKUP(A2,$B$2:$B,$C$2:$C,"Not found")
VLOOKUP works too; always set the last argument to FALSE for an exact match:
=IFERROR(VLOOKUP(A2,$B$2:$C,2,FALSE),"Not found")
6. Compare two columns row by row
When the lists are aligned and you want to know whether each row matches its neighbor:
=IF(A2=B2,"Same","Different")
= ignores case. For a case-sensitive comparison use EXACT:
=IF(EXACT(A2,B2),"Same","Different")
To color the rows that differ, select A2:B and add a conditional formatting rule with the custom formula =$A2<>$B2.
7. Compare lists on two different sheets
In a normal cell, refer to the other sheet directly:
=IF(COUNTIF(Sheet2!$A$2:$A,A2)>0,"Match","No match")
Conditional formatting rules can't refer to another sheet directly, so wrap the range in INDIRECT:
=COUNTIF(INDIRECT("Sheet2!A2:A"),$A2)>0
Replace Sheet2 with the name of your sheet. If the name contains spaces, put it in single quotes: INDIRECT("'Old customers'!A2:A").
Common problems and how to fix them
- Extra spaces: "Apple " and "Apple" don't match. Select the data and use Data → Data cleanup → Trim whitespace.
- Duplicates inside one list: use Data → Data cleanup → Remove duplicates first, or wrap results in
UNIQUE. - Case: COUNTIF, MATCH, XLOOKUP and VLOOKUP ignore case. For a case-sensitive membership test, use
=SUMPRODUCT(--EXACT($B$2:$B$100,A2))>0. - Numbers vs. text: a number and the same digits stored as text don't match. Use Format → Number to give both columns the same format, or convert with
=VALUE(A2).
Summary
| Method | Result | Best for |
|---|---|---|
| Conditional formatting (COUNTIF) | Colored matches or differences | Quick visual check |
| COUNTIF / MATCH helper column | Match / No match flag | Sorting and filtering |
| ARRAYFORMULA | Flags for the whole column | Lists that keep growing |
| FILTER (+ UNIQUE, SORT) | Live list of matches or differences | Extracting results |
| XLOOKUP / VLOOKUP | Related data from the other list | Matches with extra columns |
Row by row (= / EXACT) |
Same / Different per row | Aligned columns |
| INDIRECT in conditional formatting | Highlights across sheets | Lists on different tabs |
Working in Excel instead? See How to Compare Two Lists in Excel. And for a one-time check without any formulas, use the online list comparison tool.