How to list values that appear in both lists in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)Keep the items of the first list that the second list counts at least once: =FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0).
- Result on the example
- a 5-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF(C2:C8,A2:A9) counts each 2025 customer in the 2026 list, giving one count per name. FILTER keeps the names whose count is above 0 and spills them in the order of the first list.
=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)Result{5 rows × 1 col} "Contoso", "Fabrikam", "Northwind", "Litware", …
A2:A98 rows × 1 col: "Contoso", "Fabrikam", "Northwind", "Litware", …A range: 8 cells in one column.
C2:C87 rows × 1 col: "Fabrikam", "Litware", "Contoso", "Wingtip", …A range: 7 cells in one column.
COUNTIF(C2:C8,A2:A9){8 rows × 1 col} 1, 1, 1, 1, …COUNTIF: Counts cells that meet one condition.- range
C2:C87 rows × 1 col: "Fabrikam", "Litware", "Contoso", "Wingtip", … - criteria
A2:A98 rows × 1 col: "Contoso", "Fabrikam", "Northwind", "Litware", …
- range
COUNTIF(C2:C8,A2:A9)>0{8 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The > operator tests greater than.
FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0){5 rows × 1 col} "Contoso", "Fabrikam", "Northwind", "Litware", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A98 rows × 1 col: "Contoso", "Fabrikam", "Northwind", "Litware", … - condition1
COUNTIF(C2:C8,A2:A9)>0{8 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:A9,ISNUMBER(MATCH(A2:A9,C2:C8,0))) | spills 5 × 1 | MATCH returns a position for names it finds and #N/A for the rest; ISNUMBER turns that into TRUE and FALSE. |
Common mistakes
- Stray spaces make the same name look different. TRIM both lists if they came from different systems.
- If a name appears twice in the first list, it is listed twice. Wrap the result in UNIQUE.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
List values that appear in both lists
E2, list the customers who appear in both the 2025 list (A2:A9) and the 2026 list (C2:C8).Questions
How do I flag each row instead of listing?
Fill =IF(COUNTIF($C$2:$C$8,A2),"In both","") down next to the first list.
How do I count how many are in both?
Use =SUMPRODUCT(--(COUNTIF(C2:C8,A2:A9)>0)).
Is the comparison case-sensitive?
No. COUNTIF and MATCH treat "contoso" and "Contoso" as the same.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.