Quick, clean, and to the point

Range contains one of many values

Excel formula: Range contains one of many values
Generic formula 

To test if one of several values exists in a range of cells, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in cell F5 is:


where "rng" is the named range H4:H10 and contains the values to look for.


Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.

The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result.

Logically, any result greater than zero means that at least one value exists in the range. So, the final step is to evaluate the SUMPRODUCT result to see if its greater than zero. Any result greater than zero returns TRUE, and any result equal to zero returns FALSE.

With hard-coded values

You can also hard code the search values into the formula, using what is known as an "array constant". For example, if you want to look for 3 values: red, cyan, and magenta inside the range H2:H8, you can use:


In the above example {"red","cyan","magenta"} is the array constant, which is one way to supply multiple values in a single argument.

Partial matches or substrings

The formula above tests for equivalency only and will not find partial matches or substrings in the range. If you need to look for substrings, you can use this formula instead.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.