Lookup with 2 variables

[chrisb]

I need help filling in a table from a data dump. The data
dump looks like:
Asian Chinese Black White Other
HR 1 2 3 4 5
Finance 6 7 8 9 10
UK 11 12 13 14 15
Logistics 16 17 18 19 20
Parcelforce 21 22 23 24 25

and I need it to fill out a table that looks like:
Asian Black Chinese Other White
Finance
HR
Logistics
Parcelforce
UK
(in other words in a different order). I've tried a
lookup but I can't get it to work when looking up columns
and rows in one formula. Is there any way to do this??? It
is likely that the data dump will change each time, so I
need something as automated as possible.

 

[Mike]

You'll need a couple of MATCH() functions and an INDEX()
function to get this to work. Assuming the headers are in
row 1 and column A, and your data is in Sheet1, then put
this in cell B2 of the other sheet and fill down and
across.

=INDEX(Sheet1!$A$1:$F$6,MATCH($A2,Sheet1!$A:$A,0),MATCH
(B$1,Sheet1!$1:$1,0))

 

[JMay]

Mike Thanks for the formula. When I try it B9:C13 shows duplicated output,
but D9:F13 is OK/Correct. There a problem here; could you revisit and
comment?

 

[JMay]

No Problem,,, I just sighted the "missing "0"" (at the end of the
statement)..

....(B$8,$A$1:$F$1)) S/B ---> ...(B$8,$A$1:$F$1,0))


Thanks

 

[chrisb]

Mike,

Many thanks this has solved my problems

Reagrds,

Chris

 

[chrisb]

Mike,

Many thanks this has helped me get what I need.

Regards,

Chris Bruty