Index Match To Return Transposed Array Values

[larryg003]

Hi Guys,

Hope all well. Bit of a tricky one today - I have 13 columns (A:M) where A corresponds to a unique ID and Columns B:M correspond to monthly values. ExcelForum 1.PNG

I'm trying to create a formula that will go through each value in Column A and publish them in a column "O" and then next to them publish in order all of the values in Columns B:M. So essentially, the first value in column A will appear in 12 different rows and next to it the values in columns B:M. Then the formula will jump to the second value in column A, pull all the associated values in Columns B:M and then continue looping through this process.
ExcelForum 2.PNG

I know its some combination of index, match with a small but its been a while and I've forgotten how to do this. I have attached an excel which should clarify any questions.

Thanks SO MUCH!

Larry

[alansidman]

If you are running Excel 2010 or later this is a simple exercise using Power Query. Highlight column A. Select on the Transform Tab of Power Query the Unpivot Icon and Unpivot other columns. Close and Load.

If you are running a later version of Excel, please update your profile as it indicates you are using 2007.

[larryg003]

Thanks Alan. I unfortunately need to do this with formulas.

[JohnTopley]

in O2

=INDEX($A$2:$A$5,INT((ROWS($1:1)-1)/12)+1)

in P2

=INDEX($B$2:$M$5,MATCH($S2,$A$2:$A$5),MOD(ROWS($1:1)-1,12)+1)

[larryg003]

This is perfect! Thank you John!

[JohnTopley]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Bo_Ry]

Please try dynamic array

=CHOOSE({1,2},INDEX(A2:A5,SEQUENCE(COUNTA(B2:M5&1),,,1/COLUMNS(B2:M5))),FILTERXML("<x><m>"&TEXTJOIN("</m><m>",0,B2:M5)&"</m></x>","//m"))