dynamic index match formula to transpose values across rows and then down columns

Hello all. So I have a very tricky formula that I just can't seem to get; I'm a bit of a dabbler in excel and am afraid I just don't have what it takes to figure this one out.

I've attached a file, & here's what I need it to do:
Depending on which value (0-14, located in a2:a16) I input into k1, transpose the numbers from the matrix a1:g29 into K2:K34, reading across the rows, and then down from the last position in g. It's a bit confusing,so I tried to highlight the direction in which i want it to read, but column g will read down from different numbers. I put examples of what I would want 0 as the input to read like in L2, and what 1 as the input should read like in M2. I understand that the number of rows would be less for higher numbers input into k1, so I also have an iferror added in there. I've got the first cell formula correct in k2, but that's all I can get. If anyone can help it would be so appreciated! Thanks for looking!

~Laura

Put this formula in K2:

=IFERROR(INDEX(A$2:F$16,$K$1+1,MATCH(ROWS($1:1),A$1:F$1,0)),IF(ROWS($1:1)-5+K$1>=29,"",INDEX(G:G,$K$1+ROWS($1:1)-4)))

then copy it down to K34 at least.

Hope this helps.

Pete

Thanks both of you for your help! Both of the formulas worked, but Pete, I just copied and pasted yours & first. However, there is actually a larger table with values that I'm trying to apply this formula to, but I just can't seem to translate the formula to the right values. Is there any way you can explain to me what the -5, >=29, & -4 in reference to in the formula? It would be much appreciated. Thanks again!

~Laura

The -5 is there because you have 5 columns of horizontal data before you want to get the data going down. The 29 is there because you are using 29 rows in column G. The -4 is there because you have 5 horizontal values and because you are using cell K1 for something other than the data to be returned (i.e. num_columns_used -1)

Hope this helps.

Pete

This is the formula that you need in W3:

=IFERROR(INDEX(A$3:P$73,$T$3+1,MATCH("Dur "&ROWS($1:1),A$2:P$2,0)),IF(ROWS($1:1)-15+T$3>=92,"",INDEX(Q:Q,$T$3+ROWS($1:1)-13)))

Copy it down.

Hope this helps.

Pete

Ah you are a life saver! So it was an issue with the match formula and the "dur"... that makes sense to me now, & the formula is making sense to me now also. When I first looked at it I had no idea what most of it meant, but you helped tremendously. Thank you so much!

You're welcome - thanks for feeding back.

Some of the numbers are different also, as you had an extra row at the top compared with your earlier example.

Pete