None of the other google results have worked for me. I have two sheets, lets call them sheet 1 and sheet 2.

Sheet one looks like this



I II III IV
a 1 3 5
a 2 4 6
a 7 9 11
a 8 10 12

sheet two looks like this:

I II.1 II.2 III.1 III.2 IV.1 IV.2
a 1 2 3 4 5 6
b 7 8 9 10 11 12



If we take the "a" values in column "II" as an example. The "1" should go into the the "II.1" column in sheet 2, which is easy to get with the following vlookup:
=VLOOKUP(sheet1!$A1;sheet1!$A$2:$D$7;2;FALSE)
The tricky part now is getting the second instance of "a" (value=2) into "II.2" of the second sheet.
I'm using the following formula now, but it doesn't work consistently.
=INDEX(sheet1!B2:B4;SMALL(IF(sheet1!A2:A4=$A2;ROW(sheet1!A2:A4));COUNTIF(A2:A2;A2)))
The same will be done for columns III and IV too, but I assume i'll only have to change the "B2:B4" to "C2:C4".

If anyone has any suggestions on how to alter the second formula or a completely different approach, i'd be really grateful!