array formula that will index a horizontal array

[Beatrix]

Hi Everyone ,

I need to index an array like C3:P38 obviously below formula is not right way to do it. Is there a formula to do this or I have to re organise data in worksheet Exc ? I attached a sample workbook to clarify what I am trying to achieve. Please see column x in sp

=IF(ISNA(INDEX(Exc!$C$3:$P$38,MATCH(1,((Exc!$A$3:$A$38=$B$2)*(Exc!$C$2:$P$2 =$B9)*(Exc!$B$3:$B$38="FIXD")),0))),"",INDEX(Exc!$C$3:$P$38,MATCH(1,((Exc!$ A$3:$A$38=$B$2)*(Exc!$C$2:$P$2=$B9)*(Exc!$B$3:$B$38="FIXD")),0)))

Cheers
Yeliz

[p45cal]

I'll leave you to add the if(isna(.. bit, but in cell X8 of sheet sp, try this:
=INDEX(OFFSET(Exc!$A$3:$A$38,0,MATCH($B8,Exc!$B$2:$P$2,0)),MATCH(1,((Exc!$A $3:$A$38=$B$2)*(Exc!$B$3:$B$38="FIXD")),0))
or shorter:
=INDEX(Exc!$C$3:$P$38,MATCH(1,(Exc!$A$3:$A$38=$B$2)*(Exc!$B$3:$B$38="FIXD") ,0),MATCH($B8,Exc!$C$2:$P$2,0))
both array-entered.
Copy down.

[Beatrix]

Thanks very much p45calIt works perfectmuch appreciated..
Cheers

I'll leave you to add the if(isna(.. bit, but in cell X8 of sheet sp, try this:
=INDEX(OFFSET(Exc!$A$3:$A$38,0,MATCH($B8,Exc!$B$2:$P$2,0)),MATCH(1,((Exc!$A $3:$A$38=$B$2)*(Exc!$B$3:$B$38="FIXD")),0))
or shorter:
=INDEX(Exc!$C$3:$P$38,MATCH(1,(Exc!$A$3:$A$38=$B$2)*(Exc!$B$3:$B$38="FIXD") ,0),MATCH($B8,Exc!$C$2:$P$2,0))
both array-entered.
Copy down.