Excel formula: Cell contains one of many with exclusions

Generic formula

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0)
*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)

Summary

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. In the example shown the formula in C5 is:

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0)
*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)

where "include" is the named range E5:E9, and "exclude" is the named range G5:G6.

Explanation

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".

In the right SUMPRODUCT, SEARCH looks for all strings in the named range "exclude".

In both parts of the formula, SEARCH returns numeric positions when strings are found, and errors when not. The ISNUMBER function converts the numbers to TRUE and errors to FALSE, and the double negative converts the TRUE FALSE values to 1 and 0.

The result at this point looks like this:

=(SUMPRODUCT({1;0;0;0;0})>0)*(SUMPRODUCT({0;0})=0)

Then:

=(1>0)*(0=0)
=TRUE*TRUE
=1

Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation.

Author

Dave Bruns

Related formulas

Cell contains one of many things

We want to test each cell in B5:B11 to see if it contains any of the strings in the named range things (E5:E9). The formula we're using in C5, copied down, is: = SUMPRODUCT ( -- ISNUMBER ( SEARCH ( things , B5 ))) > 0 This formula is based on a...

Cell contains all of many things

The key is this snippet: ISNUMBER ( SEARCH ( things , B5 ) This is based on another formula ( explained in detail here ) that simply checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE. If not, the...

Get first match cell contains

In this example, we have a list of of colors in a named range called "things" (E5:E9). We want to check the text in column B to see if it contains any of these colors. If so, we want to return the name of the first color found. Working from the...

Cell contains specific text

The SEARCH function returns the position of the search string when found, and the #VALUE! error if not found. We use this fact to test whether the search string is found by using the ISNUMBER function to "catch" valid numeric positions. ISNUMBER...

Value exists in a range

The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero. Any positive number means the value was found. This means you can use the greater than...

Cell contains which things

This formula is based on the simpler formula described here. It uses the SEARCH function to look inside B5 for using cell C4 as the search string. If SEARCH returns a number, it means the search string was found. In that case, ISNUMBER will return...

If cell contains one of many things

This formula uses two named ranges : things , and results . If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don't want to use named ranges, use absolute references...

Related functions

Excel SUMPRODUCT Function

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...

Excel ISNUMBER Function

The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.

Excel SEARCH Function

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.

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.