PDA

View Full Version : [SOLVED] array formula that will index a horizontal array



Beatrix
08-16-2013, 10:19 AM
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:think: ? 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)*(E xc!$B$3:$B$38="FIXD")),0)))

Cheers
Yeliz

p45cal
08-16-2013, 02:57 PM
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
08-18-2013, 06:28 PM
Thanks very much p45cal:thumbIt works perfect:yesmuch appreciated..
Cheers:beerchug:



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.