Looking for assistance in getting comparative data from a table ( i.e data from same month last year, previous month this year, 6 month average,)

[JimBoBz]

hi There ,
I am looking to figure out how to make a summary table that would show comparative data from a table that is updated monthly.
Table 1 is updated monthly (start of the month) and has columns showing previous months data.
The values I am looking to show in a Table 2 are "the Same Month Last Year ", Previous Month This Year", "6 Month Average" and "12 Month Average".
Any help is much appreciated

 

[JamesCanale]

MrExcelPlayground8.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
2	Dept 1	94%	95%	96%	95%	92%	91%	89%	92%	94%	94%	94%	95%	96%	96%
3	Dept 2	93%	97%	92%	86%	84%	82%	75%	79%	74%	81%	86%	90%	91%	91%
4
5	14	vs Same month LY	vs Prev month TY	6M average	12M average
6	Dept 1	95%	96%	95%	94%
7	Dept 2	97%	91%	86%	84%
Sheet14

Cell Formulas
Range	Formula
A5	A5	=MAX(ISNUMBER(B2:Y2)*SEQUENCE(1,COLUMNS(B2:Y2)))
B6:B7	B6	=INDEX(B2:Y2,1,$A$5-12)
C6:C7	C6	=INDEX(B2:Y2,1,$A$5-1)
D6:D7	D6	=AVERAGE(INDEX(B2:Y2,1,$A$5-{0,1,2,3,4,5}))
E6:E7	E6	=AVERAGE(INDEX(B2:Y2,1,$A$5-SEQUENCE(12,1,0,1)))

 

[JimBoBz]

@JamesCanale - this is perfect. Thank you for the prompt response