Find word in array of cells

[AMAS]

I'm trying this formula to look at cells for a text word. It works fine as long as the word is found in the first cell in the array, but not in the remaining cells. What am I doing wrong?

=IF(ISERROR(FIND($QE$4,$AP5:$AW5)),"",$QE$4)

AMAS

 

[Marcelo Branco]

Hi,

Try this array-formula
=IF(ISERROR(MATCH(9.9999E+307,FIND($QE$4,$AP5:$AW5))),"",$QE$4)
Ctrl+Shift+Enter

HTH

M.

 

[Haseeb Avarakkan]

Hi, Try this,

=IF(ISNUMBER(MATCH("*"&$QE$4&"*",$AP5:$AW5,0)),$QE$4,"")

Or,

=IF(COUNTIF($AP5:$AW5,"*"&$QE$4&"*"),$QE$4,"")

 

[Marcelo Branco]

Hi,

Remember:
FIND is case-sensitive. If you dont want this, use SEARCH

M.

 

[AMAS]

Thanks Marcelo and Haseeb . I will try these options.

AMAS