Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find common values between two lists in Excel and Google Sheets
Topic:Duplicates & uniquenessExcel & Google Sheets
=IF(COUNTIF($B$2:$B$6,A2)>0,"In Both","")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| List A | List B | Common? |
| Apple | Banana | |
| Banana | Cherry | |
| Cherry | Apple | |
| Date | Fig | |
| Elderberry | Banana |
=IF(COUNTIF($B$2:$B$6,A2)>0,"In Both","")→In Both
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In a helper column beside List A, enter =IF(COUNTIF($B$2:$B$6,A2)>0,"In Both","") — a non-empty result means the value exists in both lists.
- 2Copy down all rows in List A.
- 3Filter the helper column for 'In Both' to extract the common values, or use COUNTIF without IF to see how many times each value appears in List B.
Tips
- To extract common values into a separate list without manual filtering, use FILTER (Sheets/Excel 365): =FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)>0).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I count the total number of common values?
Use SUMPRODUCT: =SUMPRODUCT((COUNTIF(B2:B6,A2:A6)>0)*1) — this counts how many items in List A also appear in List B.
What if a value appears multiple times in each list?
COUNTIF returns the count from List B, so >0 correctly marks it as common regardless of how many duplicates exist.
More on Duplicates & uniqueness
See all →How-to
How to remove duplicates
Remove duplicate values with one formula using UNIQUE, or strip them in place with the built-in tool. Works in Excel 365 and Google Sheets.
How-toHow to compare two columns
Check whether two columns match row by row, or find values in one column missing from another, using IF and COUNTIF.
How-toHow to get a sorted list of unique values
Extract a sorted, deduplicated list from a column using SORT+UNIQUE in modern Excel and Google Sheets, or array formulas in older versions.
How-toUNIQUE function vs Remove Duplicates in Excel and Sheets
Learn when to use the UNIQUE formula versus the Remove Duplicates tool in Excel and Google Sheets to eliminate or surface distinct values.
How-toHow to flag duplicate values
Use COUNTIF to flag duplicate values in a list with a simple formula that marks any entry appearing more than once.
How-toHow to find duplicate values
Identify which values appear more than once in a list using COUNTIF to add a Duplicate/Unique label to each row.