Index, match, large ? results based on user input for week number

Formula:

=LET(

A,E7:R29, - input range

B,E6:R6, - input range

C,D7:D29, - input range 

D,D2, - input range

E,SEQUENCE(ROWS(A)), - counts rows in A and returns an array: 1,2,3,4,etc

F,XMATCH(D,B,0), - finds the correct column in A that matches D2

G,INDEX(A,E,SEQUENCE(,F,MAX(1,F-4))), - chooses the matching column and (up to... depending on availability) 4 columns to the LEFT

H,COLUMNS(G), - number of columns in G (could be anything up to 5)

I,CHOOSECOLS(G,SEQUENCE(H-1)), - chooses all but the last column - could be 4 or fewer -  (these are the ones you want to average)

J,HSTACK(IFERROR(BYROW(I,LAMBDA(x,AVERAGE(x))),""),INDEX(G,,H),C), - calculates the average, row-by-row from I and joins it in a horizontal array with The last column of G (the data from the week in D2) and C (the titles)

TAKE(SORT(FILTER(J,INDEX(J,,2)>0),2,-1),5)) - filters the non zero values from column 2 of the stacked array (J), sorts tehm by th second column in descending order and TAKEs only the first 5

Maybe this will also help: refer to the file. I've moved the formula to B47. If you enter any of the letters defined at teh left in the formula at th epoint indicated, you can see exactly what the formula returns.

I've also corrected a minor cosmetic error in J that I just spotted.