# How to compare two lists and find matches in Excel

Check each item against the other list with COUNTIF: =IF(COUNTIF(other_list,A2),"In both","Only in list 1").

**Excel:** `=IF(COUNTIF($D$2:$D$5,A2),"Registered","Not yet")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Invited | Status |  | Registered |
| Aiko |  |  | Chidi |
| Ben |  |  | Aiko |
| Chidi |  |  | Gus |
| Dana |  |  | Fay |
| Eli |  |  |  |
| Fay |  |  |  |
| Gus |  |  |  |

Formula in B2, filled to B8: `=IF(COUNTIF($D$2:$D$5,A2),"Registered","Not yet")`

Result: Registered; Not yet; Registered; Not yet; Not yet; Registered; Registered

## How it works

COUNTIF returns how many times the name appears in the other list: 0 when it's missing, 1 or more when it's there. IF treats 0 as FALSE and any other number as TRUE, so no comparison is needed.

## Other ways

- `=IF(ISNUMBER(MATCH(A2,$D$2:$D$5,0)),"Registered","Not yet")`: MATCH returns a position or #N/A; ISNUMBER turns that into TRUE or FALSE.
- `=IF(ISNA(XLOOKUP(A2,$D$2:$D$5,$D$2:$D$5)),"Not yet","Registered")`: XLOOKUP works too, and can return a related column from the other list instead of a label.

## Common mistakes

- Lock the other list with $ before filling down.
- Names that differ only by a trailing space won't match. TRIM both lists.
- To compare row by row (is A2 the same as D2?), use `=A2=D2` instead; COUNTIF checks the whole list.

## Questions

**How do I list the names that are missing from the other list?**

Use FILTER: `=FILTER(A2:A8,COUNTIF(D2:D5,A2:A8)=0)`.

**How do I compare two columns in Google Sheets?**

The same COUNTIF formula works in Google Sheets.

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

Source: https://spreadsheetpractice.com/formulas/compare-two-lists-for-matches