How can I search for a substring in a cell, using several cells as the search argument? [Google Sheets]

Question

In Google Sheets, I'm trying to indicate whether each cell in a specific column (Let's call it "Target column") contains any of the words listed in a group of cells (Let's call it "Word warehouse").

The idea is that each cell in Target column that isn't empty AND doesn't contain any word from Word warehouse will add +1 to some other cell in the spreadsheet.

For example, if my column contains any of {"No", "Not", "None", "Negative"} then I will ignore it. If it contains anything else (and is not empty) then it will be counted.

Using Search or Vlookup doesn't help since they expect a single string value rather than a range of cells (Word warehouse).

Answer 1

You can try following formula:

=--ArrayFormula((SUM((--ISNUMBER(SEARCH(TRANSPOSE($D$1:$D$4),A1))))=0)*(A1<>""))

In example range A1:A7 is Target column and range D1:D4 is Word warehouse.

Answer 2

I may have an answer that works for you. See my sample sheet here.

The key formula, C2 in the sample sheet, is:

=QUERY(A2:B,"SELECT A WHERE UPPER(A) MATCHES '" & 
  UPPER(".*" & JOIN(".*|.*",FILTER(B2:B,B2:B<>"")) & ".*") &
  "' ",0)

where A2:A is your "target column" and B2:B is your "word warehouse". This tests each word or phrase in column A against the (filtered) list of words (or phrases) in column B, and produces a list of all of the ones that match.

By counting the total number of entries in column A, and subtracting the count of the number that matched, you get a count of all of the ones that didn't match. This can be done with this formula - D2 in my sample sheet:

=COUNTA(A2:A) - 
 COUNTA(QUERY(A2:B,"SELECT A WHERE UPPER(A) MATCHES '" & 
          UPPER(".*" & JOIN(".*|.*",FILTER(B2:B,B2:B<>"")) & ".*") & 
          "' ",0))

Note that I've made the match insensitive to case. This can easily be removed, by removing the upper function in the two places in the formula. This also matches on partial matches, eg "Catcher" matches "cat" in the word warehouse. This could also be easily changed.

I also only count one match per phrase, even if it contains several of the words in the warehouse.

Let me know if this helps.