Excel - Check If Cell Contains One Of Several Values

[jellybeard999]

Not sure if this is one for programming or software. The title might not be very clear, but hopefully the below screenshot will help... terrible fictional example, but it gets the principle across!

I have a list of values in a report (col A). I have a separate list of exceptions (col D). I am hoping to be able to have a check (col B) to identify whether any of the words in the list in col D are contained anywhere within A1, and post the result in B1, then continue to A2, result in B2 etc.

Hopefully not case sensitive, and include partial words, so it would return Y for eg chicken, STEW, stewed, Stewart, beefstew etc

Can anyone suggest a way to do this? It's fairly straightforward to do it for each value singly, but not sure how to approach this for multiple values.



Thanks for any suggestions

 

[jellybeard999]

Is this what you are after ?
https://exceljet.net/formula/cell-contains-specific-text

No as that is to check for a single value, but it did link to https://exceljet.net/formula/cell-contains-one-of-many-things which is what I'm after. Thanks I did search for it, but kept getting results for formulas which did other things. I think using the word "range" threw my searches off track!

In my example, the formula is

=SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$2,A1)))>0

Thanks @fiacha