Beginner excel question

[bmagiera]

I am using the formula below to either pull in the result only from another cell, or i pull in the date and the result see below for what the pivot table shows:

Dec-01-22 100% Green

=IF(OR(M5="(blank)",M5=""),"",IF(LEFT($AY5,3)<>$AY$1,TRIM(MID(SUBSTITUTE(M5,CHAR(10),REPT(" ",LEN(M5))),(1)*LEN(M5)+1,LEN(M5))),LEFT(M5,(LEN(M5)-6))))

If for instance the month is Dec, and the result is Dec, i just want the result (100%). If the month is Jan and the column is for Dec, then i want the first two rows Because the month mismatches the column header. I am good with what i have and it works.
But, i have another column that equals Monthly, quarterly or annually. all annual results are Dec, and all quarterly are (Mar, Jun, Sep, Dec) and i would like the formula to understand, for annual metrics. Basically i need a formula for the 3 possible options.

So if the final matches the month, it just shows result. but if it is annual, i want sep to have the dec 21 value, and aug to have the dec 2020 value

APR	MAY	JUN	JUL	AUG	SEP
Prior-3	Prior-2	Prior-1	prior	previous	current
		99%			99%

Please and thank you, i am getting stuck here.

 

[bmagiera]

Basically, i think i need to write 3 IFOR statements here.

I need quarterly to follow the same logic as annually

=IF(OR(M5="(blank)",M5=""),"",IF($F5="Annually",W5,AQ5))

 

[bmagiera]

NVM, i got it with the below, I made AQ = quarterly in the columns i needed to

=IF($F5="Annually",W5,IF($BO5=AQ$3,$BG5,IF($BN5=AQ$3,$BF5,IF($BM5=AQ$3,$BE5,IF($BL5=AQ$3,$BD5,IF($BK5=AQ$3,$BC5,IF($BJ5=AQ$3,$BB5,IF($F5=AQ$1,W5,""))))))))