Return matching values
=FILTER(A2:A,COUNTIF(B2:B,A2:A)>0)Creates a compact list of overlaps.
PASTE-READY RECIPE
Find Duplicates Across Two Columns in Google Sheets
Mark values in one list that also appear in another list.
QUICK ANSWER
The formula and where it fits.
Mark values in one list that also appear in another list. Use =IF(COUNTIF($B$2:$B,A2)>0,"Duplicate",""), then replace the sample ranges with the columns in your sheet.
COUNTIF checks whether the value from column A appears anywhere in column B.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | B | |
|---|---|---|
| 1 | List A | List B |
| 2 | ||
| 3 | ||
| 4 | ||
| 5 |
FORMULA OUTPUTAcme · Orbit · NorthRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Unique overlaps
=UNIQUE(FILTER(A2:A,COUNTIF(B2:B,A2:A)>0))Removes repeats from the result.
Case-sensitive
=IF(SUM(N(EXACT(A2,$B$2:$B)))>0,"Duplicate","")Treats Acme and ACME as different.
COMMON ERRORS
When the result looks wrong.
Blank cells are marked as duplicates
Add A2<>"" to the test.
Spaces prevent a match
Clean both lists with TRIM.
QUESTIONS
Answers before you paste.
Blank cells are marked as duplicates
Add A2<>"" to the test.
Spaces prevent a match
Clean both lists with TRIM.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.