Sheets - Return TRUE if value from list is found-CodePudding

I have two columns in Google Sheets, A,B with a long list of words. Something like this:

A: Red, yellow, blue

C: Extra

I also have an empty cell, D1, to type in. And E2 to show the result.

What I need is a formula that will return TRUE in E2 whenever any of the values from column A are found by themselves or combined with values from column C. However, if only a value from C is found or a word from outside the list it should return FALSE.

There can be one or more values from column A, separated by a " " sign.

So for example, if I type some values in D1 his is the result I would expect:

Red -> TRUE
Red Yellow -> TRUE
Yellow Extra -> TRUE
Extra -> FALSE
Blue Red Dog -> FALSE
Dog Extra -> FALSE
Cat Dog -> FALSE
Red Yellow Blue Extra -> TRUE

So to summarise, if the values are from:

Column A (one or several values): TRUE

Column C: FALSE

Column A   COLUMN C (one or several values): TRUE

Any other combination or random words from outside of the list: FALSE

Any ideas on how to achieve this? I've been trying and trying but I am not there yet. Thanks!

CodePudding user response：

You can try this: I used MAP to search each splitted value in A:C with FLATTEN. This way, each MATCH from a column would have an equal remainder divided by 3, so MOD would work for determining column in each value. If there's no match, then with IFERROR it returns 0

Now, this series of numbers I put them in a LAMBDA she called it range to be more manageable. If COUNTIF of 0 (no match) and 2 (ColB) it's greater than 0, it will return FALSE. Then it checks if there's a 1, if it does then it will be TRUE, or else FALSE ,(meaning the values would be only from ColC). Here you have a