Range Contains One of Many Values? In Excel

Yes, Excel can check if a range contains one of many values. You enter a list of values to search for, and the formula returns TRUE if any of them appear in the range, FALSE otherwise. The standard approach uses SUMPRODUCT with COUNTIF, or the newer FILTER function in Excel 365.

Formula

The most reliable formula for Excel 2019 and earlier is:

=IF(SUMPRODUCT(--(COUNTIF(range, value1)+COUNTIF(range, value2)+...))>0, "TRUE", "FALSE")

But that gets unwieldy with many values. A cleaner version uses an array constant:

=IF(SUMPRODUCT(--(COUNTIF(range, {"value1","value2","value3"})))>0, "TRUE", "FALSE")

Here, COUNTIF checks each value in the array against the range, returning a count for each. SUMPRODUCT sums those counts, and the IF returns TRUE if the sum is greater than zero.

For Excel 365, a simpler formula is:

=IF(COUNTA(FILTER(range, COUNTIF(range, value_list)>0))>0, "TRUE", "FALSE")

But that requires the values to be in a separate range, not hardcoded.

Worked Example

Suppose your range is A1:A5 containing: apple, banana, grape, orange, pear. You want to know if it contains banana or kiwi.

Using the array constant formula: =IF(SUMPRODUCT(--(COUNTIF(A1:A5,{"banana","kiwi"})))>0, "TRUE", "FALSE")

Step-by-step:

1. COUNTIF(A1:A5,{"banana","kiwi"}) returns {1,0} because banana appears once, kiwi zero times.
2. --({1,0}) converts to {1,0} (no change, but needed for logical arrays).
3. SUMPRODUCT({1,0}) sums to 1.
4. Since 1 > 0, the formula returns TRUE.

If you change the values to {"kiwi","mango"}, both counts are 0, sum is 0, and the result is FALSE.

When to Use This

Use this when you need to check a range against a list of possible values, such as:

• Validating data entry – does a cell contain any of several acceptable codes?
• Filtering rows – flag rows where a column matches any of a set of criteria.
• Conditional formatting – highlight cells that contain any of a list of keywords.
• Dashboard reporting – show a summary if any of several products were sold.

It’s more efficient than nested OR statements when the list grows beyond a few items.

How to Choose the Right Formula

Your choice depends on your Excel version and how you store the values:

• Excel 365 / 2021: Use FILTER or the SUMPRODUCT with a range reference. The FILTER approach is easier to read.
• Excel 2019 and earlier: Use SUMPRODUCT with an array constant. You can also use COUNTIF with a range reference if you put the values in a helper column.
• Values in a separate range: If your values are in cells, say D1:D3, use =IF(SUMPRODUCT(--(COUNTIF(A1:A10, D1:D3)))>0, "TRUE", "FALSE") – but you must enter it as an array formula (Ctrl+Shift+Enter) in older versions.

Common Mistakes and Pitfalls

• Forgetting to press Ctrl+Shift+Enter in older Excel versions when using array formulas – this causes wrong results or errors.
• Mismatched data types: If your range contains numbers stored as text, and your values are numbers, COUNTIF won’t match. Use TEXT or VALUE to align types.
• Wildcard characters: COUNTIF treats * and ? as wildcards. If you need literal asterisks, use ~*.
• Case sensitivity: COUNTIF is not case-sensitive. For case-sensitive matching, use EXACT in an array formula.
• Trailing spaces: Extra spaces in your list or range can break matches. Use TRIM to clean data.

Alternative Approaches

Beyond SUMPRODUCT, you have other options:

• OR function: =OR(COUNTIF(range, value1), COUNTIF(range, value2)) – works but gets long with many values.
• MATCH with ISNUMBER: =ISNUMBER(MATCH(value, range, 0)) – checks a single value, but you can combine with OR.
• Power Query / Get & Transform: For large datasets, load data into Power Query and use a merge with a list of values.
• VBA macro: For complex logic, a custom function can loop through values.

Limitations and Performance

SUMPRODUCT with COUNTIF is fast for ranges up to a few thousand cells. For very large ranges (hundreds of thousands of rows), consider using Power Query or a helper column with a VLOOKUP. Also, array constants are limited to 255 characters, so if your list is long, put it in a range instead.

Frequently Asked Questions

How do I check if a cell contains any of several substrings, not exact matches?

Use wildcards: =IF(SUMPRODUCT(--(COUNTIF(range, "*"&{"apple","banana"}&"*")))>0, "TRUE", "FALSE"). This checks if the range contains any cell that has “apple” or “banana” anywhere in it.

Can I use this in conditional formatting?

Yes. Select the range, go to Home > Conditional Formatting > New Rule > Use a formula. Enter the formula without the IF wrapper, e.g., =SUMPRODUCT(--(COUNTIF($A$1:$A$10, {"apple","banana"})))>0, and set the format.

Why does my formula return #VALUE! error?

Usually because you didn’t press Ctrl+Shift+Enter in older Excel, or because your array constant has mismatched dimensions. Ensure the range and the values array have the same number of rows/columns.

Is there a way to count how many of the values appear, not just TRUE/FALSE?

Yes. Use =SUMPRODUCT(--(COUNTIF(range, values)>0)) to count how many distinct values from your list appear at least once. For total occurrences, use =SUMPRODUCT(COUNTIF(range, values)).

Does this work with dates and numbers?

Yes, as long as the data types match. For dates, ensure both the range and the values are actual date values, not text. For numbers, beware of stored-as-text issues.

What if I want to check if a range contains ALL of many values?

Use =IF(SUMPRODUCT(--(COUNTIF(range, values)>0))=COUNTA(values), "TRUE", "FALSE"). This compares the number of distinct found values to the total number of values you’re checking.

Related

• Extend Selection to Last Cell: Excel Shortcut Guide
• Protect Your Disney Plus Account from Hackers
• Infinix Smart 7 FRP Unlock Tool: How to Bypass

in list | Values to search (comma-sep) | - | apple,banana,cherry | 0
in rng | Range to check (e.g., A1:A10) | - | A1:A10 | 0
out result | Contains any? | - | IF(SUMPRODUCT(--(COUNTIF(rng, TRIM(MID(SUBSTITUTE(list,",",REPT(" ",100)), (ROW(INDIRECT("1:"&LEN(list)-LEN(SUBSTITUTE(list,",",""))+1))-1)*100+1, 100))))>0), "TRUE", "FALSE") | 0