It appears you are just transferring data from one place to another. Perhaps:
=INDEX('Sheet1'!$B$2:$D$4,MATCH($A2,'Sheet1'!$A$2: $A$4),MATCH(B$1,'Sheet1'!$B$1:$D$1))

or something similar will work.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jose Mourinho" wrote:

I have Sheet1 laid out as follows:

Month CatA CatD CatJ
Month 1 10 50 10
Month 2 5 10 5
Month 3 5 10 10

I need to divide data into 1 sheet per Category (Cat) data i.e.

Month CatA
Month 1 10
Month 2 5
Month 3 5

Huge sheet, many categories, finding HLOOKUP ("CatA", Sheet1!RefTable, x)
very cumbersome. There must be a simpler way...