# How to list values that appear in both lists in Excel

Keep the items of the first list that the second list counts at least once: `=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)`.

**Excel:** `=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Customers 2025 |  | Customers 2026 |  | In both lists |
| Contoso |  | Fabrikam |  |  |
| Fabrikam |  | Litware |  |  |
| Northwind |  | Contoso |  |  |
| Litware |  | Wingtip |  |  |
| Adatum |  | Proseware |  |  |
| Proseware |  | Tailspin |  |  |
| Lucerne |  | Northwind |  |  |
| Margie's Travel |  |  |  |  |

Formula in E2: `=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)`

Result (spills 5 × 1): Contoso; Fabrikam; Northwind; Litware; Proseware

## 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.

## Other ways

- `=FILTER(A2:A9,ISNUMBER(MATCH(A2:A9,C2:C8,0)))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/values-in-both-lists