Hi
Use the following formulas to get two helper columns
Column U from U7:
Formula:
=1/AGGREGATE(14,6,1/((Data[Year]=Table1[@Year])*(Data[Region]=Table1[@Region])*(Data[Strategy]=Table1[@Strategy])*ROW(Data[Year])),1)-ROW(Data[#Headers])
Column V from V7:
Formula:
=AGGREGATE(14,6,(Data[Year]=Table1[@Year])*(Data[Region]=Table1[@Region])*(Data[Strategy]=Table1[@Strategy])*ROW(Data[Year]),1)-ROW(Data[#Headers])
Use this two formulas to get your desired output
In H7:
Formula:
=IFERROR(IF(F7>=INDEX(Data[Upper Quartile],$U$7,0),3,IF(F7>=INDEX(Data[Median],$U$7,0),2,IF(F7<>"",1,""))),"")
In I7:
Formula:
=IFERROR(IF(G7>=INDEX(Data[Upper Quartile],$V$7,0),3,IF(G7>=INDEX(Data[Median],$V$7,0),2,IF(G7<>"",1,""))),"")
(Copy down)
Notes: UpperQuartile-->3, Median --> 2, LowerQuartile-->1
Bookmarks