excel if range contains value then return text code example

Example: excel if range of cells contains specific text

The Generic Formula:

=COUNTIF(rng,"*"&value&"*")>0

EXAMPLE FOR THIS FORMULA:

=COUNTIF(rng,"*"&D5&"*")>0

With 'IF' included:

=IF(COUNTIF(range,"*"&value&"*"),"Yes","No")

The second method:

=ISNUMBER(SEARCH(substring,text))

EXAMPLE FOR THIS FORMULA:

=ISNUMBER(SEARCH(C5,B5))

Case sensitive version:

=ISNUMBER(FIND(substring,text))

EXAMPLE:

=IF(ISNUMBER(SEARCH(substring,text)), "Yes", "No")

If you would like to be extremely specific:

=ISNUMBER(SEARCH("apple",A1))

What this does (^) is searches for how many times the word 'apple' occurs in cell A1.