How to find matches between two lists in Excel
By the Analistable team · Updated · 5 min read
Next to list A, enter =ISNUMBER(MATCH(A2, ListB!A:A, 0)) (or =COUNTIF(ListB!A:A, A2)>0) and fill down: TRUE means the value is also in list B. In Microsoft 365, =FILTER(A2:A100, ISNUMBER(XMATCH(A2:A100, B2:B100))) returns every match in one formula, and wrapping the test in NOT() returns the values that are only in list A.
Part of our guide: How to compare two spreadsheets for differences and matches
Three questions, three formulas
| Question | Formula (Microsoft 365) |
|---|---|
| Which values are in both lists? | =FILTER(A2:A100, ISNUMBER(XMATCH(A2:A100, B2:B100))) |
| Which are only in list A? | =FILTER(A2:A100, NOT(ISNUMBER(XMATCH(A2:A100, B2:B100)))) |
| How many times is each A value in B? | =COUNTIF(B2:B100, A2:A100) |
MATCH + ISNUMBER (every version)
MATCH(A2, B:B, 0) returns the position of A2 in column B, or #N/A if it isn't there. ISNUMBER turns that into TRUE or FALSE:
=IF(ISNUMBER(MATCH(A2, Sheet2!$A:$A, 0)), "In both", "Only in A")The 0 means exact match. Without it, MATCH does an approximate match and returns wrong answers on unsorted lists.
COUNTIF (counts, not just yes/no)
=COUNTIF(Sheet2!$A:$A, A2) returns how many times A2 appears in the other list. 0 means no match; 2 or more reveals duplicates in the other list, which matters before you join the lists.
| Customer (this month) | =COUNTIF(LastMonth!A:A, A2) | Status |
|---|---|---|
| Bakery Lune | 1 | Returning |
| Hart & Co | 0 | New |
| Nordic Supply | 2 | Returning (listed twice last month) |
Partial and fuzzy matches
- Contains:
=COUNTIF(B:B, "*"&A2&"*")>0finds A2 anywhere inside a value in B. - Ignore spaces: compare
TRIM(A2)values, or clean both lists first. - Spelling differences (“Hart and Co” vs “Hart & Co”): formulas can't fix these reliably. Use Power Query fuzzy merge.
Lists in different files
Open both workbooks and click into the other file while writing the formula; Excel adds the reference such as [Customers.xlsx]Sheet1!$A:$A. COUNTIF returns #VALUE! when the other workbook is closed, but MATCH and XMATCH keep working. For a visual check of two whole sheets, see comparing two Excel sheets.
Once you know which rows match, you often want their other columns side by side. That's a join — see joining spreadsheets.
Highlight matches instead of listing them
- Select list A (for example A2:A100).
- Home → Conditional Formatting → New Rule → Use a formula.
- Enter
=COUNTIF($B$2:$B$100, A2)>0and pick a fill colour.
Every value in A that also appears in B is highlighted. Change >0 to =0 to highlight the values that are missing from B instead.
Matches across two columns at once
To match on two columns together (for example First and Last name), compare joined keys: =COUNTIFS(ListB!A:A, A2, ListB!B:B, B2)>0. COUNTIFS takes one range and criterion pair per column.
Worked example: newsletter list vs customer list
Marketing wants to know which newsletter subscribers have bought something. List A is 6 subscriber emails; list B is the customer list.
| Subscriber (A) | =ISNUMBER(MATCH(A2, Customers!A:A, 0)) | Result |
|---|---|---|
| ana@lune.fr | TRUE | Customer |
| ben@hart.co.uk | TRUE | Customer |
| cara@mail.com | FALSE | Subscriber only |
| dev@nordic.dk | TRUE | Customer |
| ed@post.net | FALSE | Subscriber only |
| fay@studio.io | FALSE | Subscriber only |
Three of six subscribers (50%) are customers: =COUNTIF(B2:B7, TRUE)/COUNTA(A2:A7) returns 0.5. The three FALSE rows are the people to target with a first-order offer.
Return a value from the matching row
Knowing that a match exists is often step one; step two is pulling a detail from the other list, such as the customer's last order date:
=XLOOKUP(A2, Customers!A:A, Customers!D:D, "No match")In Excel 2019 and earlier, use =IFERROR(INDEX(Customers!D:D, MATCH(A2, Customers!A:A, 0)), "No match"). Both return the first matching row only; if the other list has duplicates, see joining two spreadsheets on a common column.
Case-sensitive matches
MATCH, XMATCH and COUNTIF all ignore case, so “abc1” matches “ABC1”. For case-sensitive codes, combine EXACT with SUMPRODUCT:
=SUMPRODUCT(--EXACT(A2, $B$2:$B$100))>0This returns TRUE only when a value in B matches A2 letter for letter, including case.
Which formula should you use?
| Need | Use | Works in |
|---|---|---|
| Yes/no flag, any version | ISNUMBER(MATCH(...)) | All versions |
| How many times it appears | COUNTIF | All versions (other workbook must be open) |
| A list of all matches in one formula | FILTER + XMATCH | Microsoft 365, Excel 2021+ |
| Match on two columns | COUNTIFS | All versions |
| Case-sensitive | SUMPRODUCT + EXACT | All versions |
| Spelling variations | Power Query fuzzy merge | Microsoft 365 and Excel 2021 |
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| FALSE for values that look identical | Trailing or non-breaking spaces | TRIM and SUBSTITUTE(..., CHAR(160), "") |
| IDs never match | Numbers in one list, text in the other | Convert one list with VALUE or TEXT so both are the same type |
| Wrong matches on unsorted data | MATCH without the 0 argument | Always use 0 for exact match |
| Wildcard values match too much | * or ? inside the data | Escape them with ~ or use XMATCH, which is exact by default |
Excel for Mac and Google Sheets
The formulas are identical in Excel for Mac; FILTER and XMATCH need Microsoft 365 or Excel 2021 there too. In Google Sheets, =ISNUMBER(MATCH(A2, B:B, 0)) and COUNTIF work unchanged, and =FILTER(A2:A, COUNTIF(B2:B, A2:A)) lists the values from A that also appear in B in one formula, because COUNTIF accepts a range as its criterion inside FILTER. Add A2:A<>"" as a second condition to drop blank rows.
Common mistakes
- Checking list B against list A when the question is about list A — the direction decides which list's unmatched values you see.
- Whole-column COUNTIF in thousands of rows, which recalculates slowly. Use fixed ranges or a table.
- Treating a match as proof of identity: two different customers can share a name. Match on an ID or email when one exists.
Frequently asked questions
- How do I find common values in two lists in Excel?
- Use =ISNUMBER(MATCH(A2, B:B, 0)) next to the first list, or =FILTER(A2:A100, ISNUMBER(XMATCH(A2:A100, B2:B100))) in Microsoft 365.
- How do I find values in one list but not the other?
- Use =COUNTIF(B:B, A2)=0, or FILTER with NOT(ISNUMBER(XMATCH(...))).
- Why does COUNTIF return #VALUE! for another workbook?
- COUNTIF and SUMIF need the referenced workbook to be open. MATCH and XMATCH work with closed workbooks.
- How do I count how many values two lists have in common?
- Use =SUMPRODUCT(--(COUNTIF(B2:B100, A2:A100)>0)). It counts the values in list A that appear at least once in list B, and works in every Excel version.
- Can I find matches without formulas?
- Yes. Select both lists and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, which colours every value that appears in both.