Find Specified Text in Column, then Find Specified Text in Row Cells - return "yes" or "no"

[atypicalv]

I've tried searching for this, but not having any luck so far.

I am looking for a formula to search for specified text in column A, and if found in column A, it then will search a specified cell in the same row for whether it has the text "OFF" or not. If it does have "OFF", it will return a "NO", if it doesn't have "OFF", if would return a "YES".

Appreciate any assistance.

 

[atypicalv]

Thanks for all help Rick, really appreciated the effort and time. I finally got it to work with this formula, thanks to some assistance on another forum as well:

{=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55<>"OFF"))>0,"Yes",IF(MAX(--($A$4:$A$55<>$A64)),"NO","YES"))}

Cheers!

 

[Christina594]

My apologies Rick, I wrote that incorrectly