INDEX/MATCH Involving Transposing

[Statto]

I'm trying to create an efficient table whereby I can use the crossthread tool to drag in any direction and get the formulas to update properly so that the data in the table displays correctly.

I've attached an example that demonstrates what I want to achieve. However, at the moment, although the OUTPUT table correctly displays the data in order, if you look at the formulas within it, it has been manually changed so that the column references incrementally change by one letter with each cell going vertically. This is obviously not possible with a normal formula.

So what formulas can I use so that all the animal As, Bs, Cs, Ds and Es go from vertical to horizontal in order within the final table.

[vlady]

you can try this one

Please Login or Register  to view this content.

the -12 part will change depend on what row you will put the formula.

[canapone]

Hi,

in G12

Please Login or Register  to view this content.

copied across: I see same outputs.

Hope it helps

--------

Edit: sorry Vladimir, I did not mean to overlap: great approach!

[vlady]

More options the better.

[Statto]

Thanks guys for the solutions. I'm tempted to use canapone's though because it doesn't use volatile functions such as OFFSET. May be better when dealing with large amounts of data.