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

 

[fiacha]

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

 

[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

 

[dazzerd]

You can use something like this if(countifs(range, *arg*)>0,y,n), the problem with this is that your arg can't be an array so you would have to expand the function to account for everything in column d which is going to be a pain if the list is large and/or dynamic.
If that's the case your better off creating a udf in code which could be done many ways.

 

[wesimmo]

I'd use an IF and a MATCH.

If you used a named range with the values you want to check against it'll be easier to make that list dynamic, just insert cells into the named range if you have more values you want to check.

Then do MATCH using the cell value versus the range, IF its >0 then theres a match, so enter a value into your helper column based on the MATCH result, >0 then the cell value is in the list of values.