Index Match Array

[namluke]

Hi, I have an index match array that only works for one column of data, is there a way to get it to work with out changing my current set up i.e merged cells.

I have highlighted in Red the ones that don't work and in yellow the drop down.

Many Thanks

Luke

[Ace_XL]

In O3

=INDEX(INDEX($B$3:$L$14,,MATCH($O$1,$B$1:$L$1,0)):INDEX($B$3:$L$14,,MATCH($O$1,$B$1:$L$1,0)+2),MATCH($N3,$A$3:$A$14,0),MATCH(O$2,$B$2:$D$2,0))

Copy down and across

[XOR LX]

Hi,

One way. In O3 and copy across and down:

=INDEX($B$3:$L$14,MATCH($N3,$A$3:$A$14,0),MATCH($O$1&O$2,LOOKUP(COLUMN($B$1:$L$1),1/($B$1:$L$1<>"")*COLUMN($B$1:$L$1),$B$1:$L$1)&$B$2:$L$2,0))

Regards

[namluke]

Thank you both, Ace XL im I able to have anymore columns by just changing the array of cells the formula covers?

So it would be 4 columns in each "test" sections.

[Ace_XL]

Thank you both, Ace XL im I able to have anymore columns by just changing the array of cells the formula covers?

So it would be 4 columns in each "test" sections.

Yes, Change the arrays and the highlighted number for more columns

=INDEX(INDEX($B$3:$L$14,,MATCH($O$1,$B$1:$L$1,0)):INDEX($B$3:$L$14,,MATCH($O$1,$B$1:$L$1,0)+2),MATCH($N3,$A$3:$A$14,0),MATCH(O$2,$B$2:$D$2,0))