multiple cells containing specific multiple text; return value

[ccarruth]

Thought I had this but doesn't work. Spreadsheet attached. Basic need is to return a value if cells in an adjacent range contain specific multiple values. Help!

 

[Haz]

=IF(OR(B4:F4="green", B4:F4="bird"), "Valid", "")

Confirm with CTRL+SHIFT+ENTER

 

[Nebu]

Hi:

A Non-Array solution.

=IF(SUM(COUNTIF($B2:$F2,{"green","bird"}),$B2:$F2)>=1,"Valid","")

Thanks

 

[bosco_yip]

Perhaps,

=IF(SUMPRODUCT(COUNTIF(B2:F2,{"green","bird"})),"Valid","")

Or,

=IF(SUMPRODUCT(1-ISERR(FIND({"green";"bird"},B2:F2))),"Valid","")

Regards
Bosco