If range contains one of many values

This tutorial shows how to check if a range contains at least one of many items and return a value using an Excel formula, with the SUMPRODUCT and COUNTIF functions

EXCEL FORMULA 1. If range contains one of many values

EXCEL

If range contains one of many values

=SUMPRODUCT(COUNTIF(C5:F5,$J$5:$J$7))>0
GENERIC FORMULA

=SUMPRODUCT(COUNTIF(rng,rng_values))>0

ARGUMENTS
rng: A range that contains values that you want to check if they contain at least of the values you are testing for.
rng_values: A range of values that that you want check for if at least one of them is captured in the selected range.

EXPLANATION

This formula uses the SUMPRODUCT and COUNTIF functions to count the number of occurrences of the specified values in the selected range. If the number of occurrence is greater than 0 the formula will return a value of TRUE, otherwise it will return a value of FALSE.

In this example only the first and third ranges contain at least one of the three values in the values list. Therefore, the formula has return a value of TRUE for these ranges. The second range doesn't contain any of the three values in the values list. Therefore, the formula has returned a value of FALSE for this range.

RELATED TOPICS

Related Topic Description Related Topic and Description
If cell contains one of many values How to check if a cell contains at least one of many items and return a value

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
SUMPRODUCT Function The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria