**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,"*`

“RAN`GE 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.

## Leave a Comment