< Go Back

Range contains one of many values

HOW TO CHECK IF A RANGE CONTAINS ONE OF MANY VALUES IN EXCEL

EXAMPLE

Figure 1. Example of checking a range which contains one of many values

STANDARD FORMULA

=SUMPRODUCT(COUNTIF(VALUE,"*“RANGE TO FIND"*"))>0

EXPLANATION

To check if a range of cells has one of several values, you can use the “SUMPRODUCT” formula-based function. In the above-given example, the formula in cell E4 is:

=SUMPRODUCT(COUNTIF(C4:D4,"*"$G$4:$G$9"*"))>0

HOW THE “SUMPRODUCT” FUNCTION WORKS

From the example above, compare each of the items in the “range” to the items in values 1, 2. The “outcome” column gives an array of TRUE or FALSE values. Since the SUMPRODUCT accepts only one array, it sums up the array items and returns the result. The double hyphen or the double unary transforms the TRUE value to 1 and the FALSE value to 0.

Evaluation: Any result above zero (i.e. result > 0) logically implies that a minimum of one value is present in the range. The last step, therefore, is to test the result of the SUMPRODUCT formula to ascertain if it’s above zero. Any result equals zero or greater than zero returns FALSE and TRUE respectively.

USING HARD-CODED VALUES

Similarly, you can hardcode the search values into the SUMPRODUCT formula using an “array constant”.  For instance, if you would like to search for 3 values, like Mango, Banana inside the range G4:G9, you can use:

=SUMPRODUCT(COUNTIF(G4:G9,{"mango","banana"}))>0

{“Mango”, “Banana”} is the array constant in the cited example. It is a way to provide several values in a single argument.

Substrings or Partial matches

The formula above checks only for exact matches AND WILL NOT locate any substrings or partial matches in the range. If you are looking for substrings or partial matches, you can use this formula as an alternative.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar