How to express the "*" denoting any text containing part of text when using IF function?

Question

When using this formula:
=if(AND('CASES LIST'!R4="No",P5="*neuro*"),"check","")

I am looking for the cell P5 if the text contains "neuro" in the text but it doesn't work when I add star* before and after.

Answer 1

Wildcards are not supported in simple IF formulas

Use the following

=if(AND($R$4="No";REGEXMATCH(P5;"neuro"));"check";"")

You can even turn it to an array using:

=ArrayFormula(if(($R$4="No")*(REGEXMATCH(P5:P,"neuro")),"check",""))

Functions used:

• ArrayFormula
• REGEXMATCH