Google Sheets: Need way to check if [text string] exists in a cell containing comma-separated text strings

Question

Here is the Google Sheet, which should be self-explanatory:

https://docs.google.com/spreadsheets/d/1WqSXvM2Ocasc9l16i6mETzYumc3iMbxNM0xZeaPvEb4/edit?usp=sharing

As you can see, I am getting a false positive using the SEARCH (and also the FIND) function.

Looks like I need a way to tell the function to only consider a match if the value occurs as an individual value within the comma-separated text string, but I don't know how to best go about it in a robust manner.

Secondarily, I need to count the number of times the value occurs in the text string.

Any help will be greatly appreciated!

Answer 1

=ARRAYFORMULA(IF(OR(C11=SPLIT(C6,", ",0)),"YES","NO"))
=SUMPRODUCT(C11=SPLIT(C6,", ",0))

• C11 Value to be searched in the list
• C6 The List
• SPLIT the list by the delimiter(the comma) and compare with the test value(C11)