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 to BX122 contains a date (not all dates for a month are present)
K4 contains the date I need to report on
AT147 to BX166 contain the processing status by site for each date.

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

Right now, if I find an "R" or "Y" anywhere in the AT122 to BX122 array, it
shows in the reporting cell, even though the date does not match.

I would appreciate any help I can get with this.

THank you

 

[Raul at work]

I decided to apply the Keep it Simple rule and I came up with the following
to give me the reporting results I was looking for:

First I set up a range of cells to display the overall status for a day:

=IF((COUNTIF(AV147:AV166,"R"))>0,"R",IF((COUNTIF(AV147:AV166,"Y"))>0,"Y","G"))

Then I tested the above range in the final results cells to determine the
status.

=IFERROR(OFFSET($AV$122,24,MATCH(H$4,$AV$122:$BZ$122,0)-1),"G")