How to Check If a Value Exists in a List in Excel

To check whether a value appears in a list, count how many times it occurs with COUNTIF and test whether that count is greater than zero: =COUNTIF($A:$A,B1)>0 returns TRUE when B1 appears anywhere in column A and FALSE when it doesn't. ISNUMBER(MATCH()), XMATCH and XLOOKUP do the same job with different trade-offs around wildcards, calculated arrays and what they give back.

What does =COUNTIF($A:$A,B1)>0 do?

It returns TRUE if the value in B1 appears at least once anywhere in column A, and FALSE if it doesn't. COUNTIF($A:$A,B1) counts the cells in column A that equal B1, and >0 turns that count into a yes-or-no answer. Put it in C1 and fill down: B1 is a relative reference, so row 2 checks B2, row 3 checks B3, and so on, while the whole-column reference $A:$A stays fixed.

=COUNTIF($A:$A, B1)>0

Three things to know about the match itself: it's an "equals" test, not a "contains" test; it ignores case, so "acme" matches "Acme"; and COUNTIF reads * and ? in B1 as wildcards, so a value like "A*" matches anything starting with A.

How do I return Yes/No or 1/0 instead of TRUE/FALSE?

Wrap the same test in IF and supply the two results you want. The test doesn't change; only the output does.

' TRUE / FALSE
=COUNTIF($A:$A, B2)>0

' Yes / No
=IF(COUNTIF($A:$A, B2)>0, "Yes", "No")

' 1 / 0
=IF(COUNTIF($A:$A, B2)>0, 1, 0)
=--(COUNTIF($A:$A, B2)>0)       ' same result: -- converts TRUE/FALSE to 1/0

' Leave the result empty when the lookup cell is empty
=IF(B2="", "", COUNTIF($A:$A, B2)>0)

Return 1/0 if you plan to add the results up. SUM ignores TRUE and FALSE values stored in cells, so summing a column of TRUE/FALSE results returns 0.

What does a worked example look like?

Column A holds six approved vendors (A2:A7, with a header in A1), and column B holds five vendor names from incoming invoices (B2:B6). Enter these formulas in row 2 and fill them down to row 6:

C2:  =COUNTIF($A:$A, B2)>0
D2:  =IF(COUNTIF($A:$A, B2)>0, "Yes", "No")
E2:  =--(COUNTIF($A:$A, B2)>0)

Row	A: Approved	B: Invoice	C: TRUE/FALSE	D: Yes/No	E: 1/0
2	Acme	Coastal	TRUE	Yes	1
3	Brightline	Granite	FALSE	No	0
4	Coastal	acme	TRUE	Yes	1
5	Delta Supply	Harbor	FALSE	No	0
6	Evergreen	Evergreen	TRUE	Yes	1
7	Fulton

=SUM(E2:E6) returns 3: three of the five invoice vendors are approved. =COUNTIF(C2:C6, TRUE) also returns 3 if you kept the TRUE/FALSE column. Row 4 returns TRUE even though the invoice says "acme" and the list says "Acme", because COUNTIF ignores case.

When should I use ISNUMBER(MATCH()) or XLOOKUP instead?

Use ISNUMBER(MATCH()) or ISNUMBER(XMATCH()) when you want TRUE/FALSE but the list is an array calculated inside the formula rather than a cell range. Use ISNUMBER(XMATCH()) when the values might contain * or ?, because MATCH, like COUNTIF, treats them as wildcards (or escape them with ~). Use XLOOKUP when you want something back other than TRUE/FALSE, such as the matched value, a value from a neighboring column, or your own "not found" message.

' TRUE/FALSE, any Excel version (the 0 means exact match)
=ISNUMBER(MATCH(B2, $A:$A, 0))

' TRUE/FALSE, Excel 365/2021+ (exact match by default, no wildcards)
=ISNUMBER(XMATCH(B2, $A:$A))

' Return the matched value, or a message when it's missing (Excel 365/2021+)
=XLOOKUP(B2, $A$2:$A$7, $A$2:$A$7, "Not in list")

' Return a related column, e.g. payment terms in F (Excel 365/2021+)
=XLOOKUP(B2, $A$2:$A$7, $F$2:$F$7, "Not approved")

MATCH returns the position of the value when it finds it and #N/A when it doesn't, and ISNUMBER converts that into TRUE or FALSE. XLOOKUP's fourth argument, if_not_found, replaces the #N/A with your own text. In the worked example, =XLOOKUP(B3, $A$2:$A$7, $A$2:$A$7, "Not in list") returns "Not in list" for Granite, and B4 ("acme") returns "Acme", the list's own spelling.

Why doesn't =IF(COUNTIF(C2, A1#))=0, 0,1) work?

It has two problems: the COUNTIF arguments are in the wrong order, and the extra parenthesis after A1# closes the IF before the =0 test, so Excel won't accept the formula as typed. To return 1 when C2 is in the spill range that starts at A1, and 0 when it isn't, use:

=IF(COUNTIF(A1#, C2)=0, 0, 1)

' Same result, shorter
=--(COUNTIF(A1#, C2)>0)

COUNTIF's first argument is where to look and the second is what to look for. With COUNTIF(C2, A1#), Excel tests C2 against every item in A1# and spills one 0 or 1 per item, which answers a different question. A1# is a spill reference: it points to the whole range that the dynamic array formula in A1 (for example =UNIQUE(...) or =SORT(...)) spills into, and it resizes when that result grows or shrinks, so the check always covers the current list. Spill references require Excel 365 or Excel 2021 or later.

To check several values at once, pass them as the second argument and the formula spills one result per value:

' One TRUE/FALSE per value in C2:C20
=COUNTIF(A1#, C2:C20)>0

' When the values to check are also a spill starting at C2
=COUNTIF(A1#, C2#)>0

COUNTIF's first argument must be an actual range, such as a cell range or a spill reference like A1#. It won't work on an array calculated inside the formula, such as COUNTIF(UNIQUE(A2:A100), C2). In that case, switch to XMATCH or MATCH, which accept arrays: =ISNUMBER(XMATCH(C2, FILTER(A2:A100, B2:B100="Active"))).

What formula matches words in A1:A10 cells with B1:B10 in Excel?

To flag each word in A1:A10 that also appears anywhere in B1:B10, put =COUNTIF($B$1:$B$10, A1)>0 in C1 and fill it down to C10. The dollar signs lock the list so it doesn't shift as you copy, and A1 stays relative so each row checks its own word.

' One formula per row (any version): enter in C1, fill down to C10
=COUNTIF($B$1:$B$10, A1)>0

' One formula for all ten rows (Excel 365/2021+): spills into C1:C10
=COUNTIF(B1:B10, A1:A10)>0

' Same-row comparison only (A1 vs B1, A2 vs B2, ...)
=A1=B1
=EXACT(A1, B1)      ' case-sensitive version

If "match" means a cell contains the word rather than equals it, use wildcards or SEARCH:

' Does any cell in B1:B10 contain the word in A1? ("north" matches "North Region")
=COUNTIF($B$1:$B$10, "*"&A1&"*")>0

' Does A1 contain any of the words listed in B1:B10?
=SUMPRODUCT(ISNUMBER(SEARCH($B$1:$B$10, A1))*($B$1:$B$10<>""))>0

The ($B$1:$B$10<>"") part matters. SEARCH treats an empty search term as a match at position 1, so one blank cell in B1:B10 would make every row return TRUE. Likewise, guard against a blank A1 in the first formula: it turns the pattern into "**", which matches any text. Both substring checks also match inside longer words: "cat" is found in "category".

How do I compare two lists and pull out the matches or the missing items?

In Excel 365/2021+, combine FILTER with the array form of COUNTIF. COUNTIF returns one count per item, and FILTER keeps the items whose count is zero (missing) or above zero (matched). Using the worked example:

' Invoice vendors NOT on the approved list: returns Granite, Harbor
=FILTER(B2:B6, COUNTIF(A2:A7, B2:B6)=0, "All approved")

' Invoice vendors that ARE on the list: returns Coastal, acme, Evergreen
=FILTER(B2:B6, COUNTIF(A2:A7, B2:B6)>0, "No matches")

' How many invoice vendors are approved, any version: returns 3
=SUMPRODUCT(--(COUNTIF(A2:A7, B2:B6)>0))

To see the overlap in place instead of extracting it, use the same test as a conditional formatting formula: select B2:B6 and add the rule =COUNTIF($A:$A, B2)=0 to shade unapproved vendors. The guide to highlighting duplicates covers the two-column version in more detail. If what you actually need is one clean list with no repeats, remove duplicates instead of flagging them.

Can I make the check case-sensitive?

Yes, with EXACT. COUNTIF, MATCH, XMATCH and XLOOKUP all ignore case, but EXACT compares text case-sensitively, and SUMPRODUCT lets it run against the whole list in any Excel version without Ctrl+Shift+Enter.

=SUMPRODUCT(--EXACT(B2, $A$2:$A$7))>0

In the worked example, this returns FALSE for "acme" in B4, where COUNTIF returned TRUE. Keep the range bounded. SUMPRODUCT evaluates every cell you give it, so a whole-column reference can make the workbook slow.

Which method should I use?

Use COUNTIF(...)>0 by default. It works in every version and is easy to read. Switch to ISNUMBER(XMATCH()) when the values can contain wildcard characters or the list is a calculated array, to XLOOKUP when you need a value back, and to EXACT when case matters.

Formula	Returns	Excel version	Watch out for
COUNTIF(A:A,B2)>0	TRUE/FALSE	All	Reads * and ? as wildcards; needs a real range or spill reference
ISNUMBER(MATCH(B2,A:A,0))	TRUE/FALSE	All	Reads * and ? as wildcards; leaving out the 0 gives approximate match
ISNUMBER(XMATCH(B2,A:A))	TRUE/FALSE	365, 2021+	Literal match by default; wildcards only with match_mode 2
XLOOKUP(B2,A:A,A:A,"No")	Matched value or your message	365, 2021+	Returns the list's spelling, not TRUE/FALSE
SUMPRODUCT(--EXACT(B2,A2:A7))>0	TRUE/FALSE	All	The only case-sensitive option here; keep the range bounded

What are common mistakes when checking if a value exists in a list?

Most wrong answers come from argument order, references that shift as you fill down, or values that look identical but aren't.

• Reversing COUNTIF's arguments. The range to search comes first and the value comes second. COUNTIF(C2, A1#) checks C2 against each list item and spills a column of 0s and 1s instead of returning one answer.
• Leaving out MATCH's 0. Without it, MATCH defaults to approximate match, which assumes sorted data and can report a position for a value that isn't in the list, so ISNUMBER returns TRUE.
• Unanchored bounded ranges. =COUNTIF(A2:A100, B2) filled down becomes A3:A101, then A4:A102, so the top of the list drops out and values near the top start returning FALSE. Use $A$2:$A$100, $A:$A, or an Excel Table column.
• Invisible differences. Trailing spaces and non-breaking spaces from web or PDF exports make a visible match return FALSE. Compare =LEN(B2) with what you expect. TRIM removes ordinary spaces but not the non-breaking space CHAR(160), so run SUBSTITUTE(B2, CHAR(160), " ") first. Numbers stored as text cause the same problem with MATCH, XMATCH and XLOOKUP, although COUNTIF treats 1001 and "1001" as equal. COUNTIF has the opposite trap: it compares numeric text as numbers with 15-digit precision, so text IDs longer than 15 digits, such as card numbers, can report false matches. ISNUMBER(MATCH()) or EXACT avoids this.
• Wildcard characters in the data. COUNTIF and MATCH read * and ? as wildcards, so a lookup value of "A*" returns TRUE if any entry starts with A. Use XMATCH, or escape the character with a tilde ("A~*").
• Summing TRUE/FALSE results. =SUM(C2:C6) returns 0 on a column of TRUE/FALSE because SUM ignores logical values in ranges. Return 1/0, or count with =COUNTIF(C2:C6, TRUE).