Test cells for values if date matches

[Raul]

I have the following formula:

=IF($AT$122:$BX$122=K$4,(IF(OR($AT$147:$BX$166="R"),"R",IF(OR($AT$147:$BX$166="Y"),"Y","G"))),"G")

AT122:BX122 - Contains a work of date.
K4 - Contains the date to be reported on.
AT147:BX166 - Contains a status for a processing site ("R", "Y", or "G")

I am trying to get the reporting cell to display an "R" if any of the cells
in the column that has the date that matches K4 has an "R" in it. If there
are no "R"s in that column, then display a "Y" if there are any "Y"s in the
column. If there are no "R" or "Y" values, then display a "G".

At this point, the reporting cell shows an "R" or a "Y" if any cell in the
AT147:BX166 range contains an "R" or a "Y", regardless of the date.

Can someone help?

Thank you

 

[Eduardo]

Hi,
try

=IF(AND($AT$122:$BX$122=K$4,$AT$147:$BX$166="R"),"R",IF(AND($AT$122:$BX$122=K$4,$AT$147:$BX$166="Y"),"Y","G"))