Return true/false on [strings in a cell] being found in [strings in another cell]?

Question

Cell A1 contains multiple strings, eg "CAT DOG RAT GNU";

Cell B1 contains multiple strings, eg "RAT CAT";

How can I run a test (using formula in C1) to find if all the strings in B1 are present in cell A1?

• Returning true/false would be good
• Strings not necessarily in the same order, as example above
• The number of items can vary
• Multiple instances not a problem, so long as they're there
• But returns true only if all items in cell B1 are present in cell A1.

So far I've tried transposed-split arrays with vlookups and matches, counts, etc, but nothing working for me. (And maybe regex won't do it as can't loop for each string?)

Answer 1

you can try:

=ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(B1, " "), 
 SUBSTITUTE(A1, " ", "|"))))))=1, TRUE))

for more precision you can do:

=ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(B1, " "), 
 "^"&SUBSTITUTE(A1, " ", "$|^")&"$")))))=1, TRUE))

then for case insensivity:

=ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(LOWER(B1), " "), 
 "^"&SUBSTITUTE(LOWER(A1), " ", "$|^")&"$")))))=1, TRUE))

---

and true ArrayFormula would be:

=ARRAYFORMULA(IF((A1:A<>"")*(B1:B<>""), IF(REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 REGEXMATCH(IF(SPLIT(B1:B, " ")<>"", SPLIT(LOWER(B1:B), " "), 0), 
 "^"&SUBSTITUTE(LOWER(A1:A), " ", "$|^")&"$"))),,999^99)), "FALSE"), FALSE, TRUE), ))