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 to find matches between two lists in Excel

By the Analistable team · Updated 2026-10-07 · 2 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
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.

This month's customers checked against last month's
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&"*")>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

Select list A (for example A2:A100).
Home → Conditional Formatting → New Rule → Use a formula.
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.

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.