Converting a table X and Y values in to Y and X values Apologies for the rubbish

Question

Converting a table X and Y values in to Y and X values

Apologies for the rubbish title. It's hard to explain what I mean without an example.

I have data like this (the gaps are there so hopefully this can be achieved with the gaps still there):

SECTION 1

Range: Z2 to AT308

What I need is that data above in the following format:

SECTION 2

Range: AZ1 to CQ17

I have tried the following formula: =INDEX($AA$3:$AT$308,MATCH(BA$1,$Z:$Z,0),MATCH($AZ2,$AA$2:$AT$2,0)) but this returns a zero for all when filled down and across.

Could the extra lines in section 1 be messing this up? I'm not sure what to try next. Hopefully someone can point me in the right direction. Thanks in advance!!

Week ANDREW JOHN 20/10/2014 5.00 0.00 0 0 0 0 27/10/2014 5.00 0.00 0 0 0 0 03/11/2014 5.00 0.00 0 0 0 0

Explanation / Answer

Hi..

Try this in BA2:

=INDEX($AA$3:$AT$308,MATCH(BA$1,$Z$3:$Z$308,0),MATCH($AZ2,$AA$2:$AT$2,0)).

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---