Check Cell If Contains One of Many with Exclusions

Check Cell If Contains One of Many with Exclusions1

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings?

In this article, we will discuss how to check if a cell contains more than one given string and exclude other given strings by using a formula. You can create a new nested formula by using the SEARCH function (google sheets or Excel), the ISNUMBER function and the SUMPRODUCT function.

The generic formulas are as follows.

= ( SUMPRODUCT ( -- ISNUMBER (SEARCH(INCLUDE_STRINGS, TEXT_STRING))) > 0 ) * ( SUMPRODUCT ( -- ISNUMBER (SEARCH(EXCLUDE_STRINGS, TEXT_STRING))) = 0 )

Check Cell If Contains One of Many with Exclusions in Google Sheets & Excel

Suppose you have two data tables A3:B12, E1:F4, you want to check whether the cell area A3:A12 contains one of the values in the cell range E2:E4, but does not contain any of the values in the cell range F2:F3. You can build the following google sheets or Excel formulas based on the above general formula.

=(SUMPRODUCT(--ISNUMBER(SEARCH(INCLUDE_STRINGS,A4)))>0) * (SUMPRODUCT(--ISNUMBER(SEARCH(EXCLUDE_STRINGS,A4)))=0)

where INCLUDE_STRINGS is the named range E2:E4, and EXCLUDE_STRINGS is the named range F2:F3.

You can enter the above formula in cell B4 and press Enter to apply the formula.

Let’s see how this formula works:

= ( SUMPRODUCT ( — ISNUMBER (SEARCH(INCLUDE_STRINGS, A4))) > 0 )

Check Cell If Contains One of Many with Exclusions1

This formula is used to check whether cell A4 contains INCLUDE_STRINGS range of strings, SEARCH function will find from A4 contains INCLUDE_STRINGS range of strings, if it exists, will return the position of the string in cell A4, otherwise return # VALUE!

ISNUMBER formula will SEARCH function return value into TRUE or FALSE, when the search function returns a number, ISNUMBER function will return TRUE, otherwise it will return FALSE.

The double negative sign before ISNUMBER converts the TRUE or FALSE value returned by the ISNUMBER function to 1 and 0.

=(SUMPRODUCT(–ISNUMBER(SEARCH(EXCLUDE_STRINGS,A4)))=0)

Check Cell If Contains One of Many with Exclusions1

This formula is used to check whether the cell does not contain any of the values in F2:F3. Where the SEARCH function is used to perform a lookup string and return the position of the string in cell A4 or return the #VALUE! error message.

Related Functions

  •  Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Google Sheets SUMPRODUCT function
    The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Related Posts

Excel Array Formula

An array formula is a special formula that is converted from a normal formula by the Ctrl+Shift+Enter shortcut. Excel automatically adds curly brackets "{}" at the beginning and end of an array formula. The essence of the array formula is ...

Excel Array

What is Excel Array? In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values. The dimension ...

Cash Denomination Calculator

Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, ...

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Sidebar