Check if cell contains all comma separated values

Question

I have a sheet with the following data:

| Text                                    | Value    |  Value   |  Value   |
|:----------------------------------------|:---------|:---------|:---------|
|Jax and Jax friend Kung Lao fight Raiden | jax      | kung lao | raiden   |
|Jax and Jax friend Kung Lao fight Raiden | kitana   | kung lao | raiden   | 

And the following formulas:

=SUMPRODUCT( -- ISNUMBER(SEARCH(B1:D1;A1)))=COUNTA(B1:D1)
=SUMPRODUCT( -- ISNUMBER(SEARCH(B2:D2;A2)))=COUNTA(B2:D2)

Which returns:

TRUE
FALSE

This is working as expected. I get TRUE of all values is found in the text-cell, and FALSE if one or all is missing.

Now, I want to modify so instead searching in multiple cells, I only want to search in one comma separated cell. Like this:

| Text                                    | Values                 | Formula |
|:----------------------------------------|:-----------------------|:--------|
|Jax and Jax friend Kung Lao fight Raiden | jax,kung lao,raiden    | TRUE    |
|Jax and Jax friend Kung Lao fight Raiden | kitana,kung lao,raiden | FALSE   |

I've tried with =SUMPRODUCT( -- ISNUMBER(SEARCH({B2};A2)))=COUNTA({B2}) but it doesn't work.

Answer 1

Please see also:

=AND(ARRAYFORMULA(REGEXMATCH(A1,"(?i)"&SPLIT(B1,",")&"( |$)")))

• (?i) - case insensitive
• ( |$) - space or end of text, to match whole words only.

---

ArrayFormula variation for cell C1:

=ArrayFormula(TRANSPOSE(NOT(REGEXMATCH(QUERY(TRANSPOSE(filter(REGEXMATCH(A:A,"(?i)"&SPLIT(B:B,",")&"( |$)"),A:A<>"")),,10^99),"FALSE"))))

Answer 2

Try splitting the comma delimited list and using the split array like a range of cells.

=SUMPRODUCT(--isnumber(search(split(B2, ",", true, true), A2)))=counta(split(B2, ",", true, true))
'full word search
=SUMPRODUCT(--isnumber(search(text(split(B2, ",", true, true), " @ "), text(A2, " @ "))))=counta(split(B2, ",", true, true))