Analistable

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

Formulas for comparing two lists
QuestionFormula (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.

This month's customers checked against last month's
Customer (this month)=COUNTIF(LastMonth!A:A, A2)Status
Bakery Lune1Returning
Hart & Co0New
Nordic Supply2Returning (listed twice last month)

Partial and fuzzy matches

  • Contains: =COUNTIF(B:B, "*"&A2&"*")>0 finds 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

  1. Select list A (for example A2:A100).
  2. Home → Conditional Formatting → New Rule → Use a formula.
  3. Enter =COUNTIF($B$2:$B$100, A2)>0 and 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.

Subscribers checked against customers
Subscriber (A)=ISNUMBER(MATCH(A2, Customers!A:A, 0))Result
ana@lune.frTRUECustomer
ben@hart.co.ukTRUECustomer
cara@mail.comFALSESubscriber only
dev@nordic.dkTRUECustomer
ed@post.netFALSESubscriber only
fay@studio.ioFALSESubscriber 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))>0

This returns TRUE only when a value in B matches A2 letter for letter, including case.

Which formula should you use?

Choosing a match formula
NeedUseWorks in
Yes/no flag, any versionISNUMBER(MATCH(...))All versions
How many times it appearsCOUNTIFAll versions (other workbook must be open)
A list of all matches in one formulaFILTER + XMATCHMicrosoft 365, Excel 2021+
Match on two columnsCOUNTIFSAll versions
Case-sensitiveSUMPRODUCT + EXACTAll versions
Spelling variationsPower Query fuzzy mergeMicrosoft 365 and Excel 2021

Troubleshooting

Matches you expected but didn't get
SymptomCauseFix
FALSE for values that look identicalTrailing or non-breaking spacesTRIM and SUBSTITUTE(..., CHAR(160), "")
IDs never matchNumbers in one list, text in the otherConvert one list with VALUE or TEXT so both are the same type
Wrong matches on unsorted dataMATCH without the 0 argumentAlways use 0 for exact match
Wildcard values match too much* or ? inside the dataEscape 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.

Related guides