Search Cells for a Particular Word

[juleskaynel]

I am looking for an excel formula for the below:

If A1, or A2, or A3, or A4 contain the Letter Y, then add the word "YES" to cell A5.

A1 A2 A3 A4 A5
Y Y N N YES

 

[CWatts]

You may use this if the cells don't have much text in them:

=IF(ISNUMBER(FIND("Y",C2,1)),"YES","")

Replace FIND with SEARCH if you do not want it to be case sensitive. If the cells have lots of characters, you may need to test each cell individually.

 

[Rick Rothstein]

Another possibility for the non-case sensitive formula...

=IF(COUNTIF(A1:A4,"Y"),"Yes","")

 

[azumi]

Another try

=IF(COUNTIF(A1:A4,"Y")>=1,"Yes","")

 

[CWatts]

ADVERTISEMENT

I need to go to bed because the first post I made is useless... If a mod edited it, I wouldn't be offended.

=IF(ISNUMBER(FIND("Y",A1&A2&A3&A4,1)),"Yes","")

 

[My Aswer Is This]

My mistake

 

[Rick Rothstein]

Another try

=IF(COUNTIF(A1:A4,"Y")>=1,"Yes","")

That is the same formula I posted except that you added the unneeded test to see if the count is equal to or greater than 1 (try it without the direct test and you will see it still works).