Adapt formula for one of many choices

[ChristineJ]

I use the following formula in cell D12 to return TRUE or FALSE based on whether an entry in cell B5 matches one of the choices in range D5:D9.

=SUMPRODUCT(--(B5=D5:D9))>0

As an example, the following colors appear in D5:D9:
D5 red
D6 green
D7 yellow
D8 blue
D9 white

If "white" (or an other color listed) s entered in cell B5, cell D12 returns TRUE. Anything other than these colors returns FALSE.

I'd like to adapt this so that if any of those colors appear in the string in cell B5, it will return TRUE. For example, all of these would return TRUE:

(1) a bluebird, (2) tall green tree, (3) yellow flower, (4) barred, (5) bright whiteness

No color will ever appear twice in the string, and only one color will every appear in the string.

Appreciate help with this! C

 

[Tetra201]

See if the following formula works for you:

=OR(COUNTIF(B5,"*"&D5:D9&"*"))

 

[ChristineJ]

See if the following formula works for you:

=OR(COUNTIF(B5,"*"&D5:D9&"*"))

It does work - perfectly! I appreciate your quick response. Very helpful. CJ

 

[Tetra201]

Glad to have helped. Happy Holidays!