if cell in range (partially) matches any value in list-CodePudding

I am trying to get "Okay" if any cell in my sheet Information $46:$46 matches text that is listed on another sheet in Lists D6:D17. My Formula works if the cells in Information only contain that value that is mentioned, the problem I now have is that the cells in Information sometimes contain multiple values

For example: I am looking for the words red, blue and purple, If any cell in Information contains any of these in any way the result should be "Okay". Some cells will contain "red and green", the result should still be "Okay" because the cell contains red

=IF(SUMPRODUCT(COUNTIF(Information!$46:$46,'lists'!D6:D17)),"okay","")

I tried making the list an array or use * but so far I have not succeeded

CodePudding user response：

In my example the formula is like this:

=IF(SUMPRODUCT(COUNTIF(Information;"*"&D6:D8&"*"));"okay";"")

In your workbook the formula would be:

=IF(SUMPRODUCT(COUNTIF(Information!$46:$46;"*"&Lists!$D$6:$D$8&"*"));"okay";"")