Array formula - really struggling with this

[manc]

Good afternoon,

Really struggling with this guys, hope you can help.

My data is as follows:

A1:c#9 B1:c#8 C1:c#7 D1:c#6 E1:c#5 F1:c#4 G1:c#3 H1:c#2 I1:c#1
A2:TRUE B2: FALSE C2: TRUE D2: FALSE E2: TRUE F2: FALSE G2:TRUE H2:FALSE I2:FALSE

My array formula has to go horizontally, so in F2 I have:
IFERROR(INDEX($A$2:$I$2,SMALL(IF($A$1:$I$2=TRUE,ROW($A$1:$I$2)-MIN(ROW(1:1))+1),COLUMNS($A:A))),"")

I then copy this formula across to R2

The formula is almost correct in the sense that it displays the correct number of TRUE results, i.e. J2=C#9 K2=C#9 L2=C#9 M2=C#9
However the correct result should be J2=C#9, K2=C#7, L2=C#5, M2=C#3

Please show me where I am going wrong. I have tried all kinds of slight amendments to no avail and there is only limited help regarding this kind of query on the web.

Best regards
manc

 

[pgc01]

Hi

You said that your formula is going horizontally, so you should use COLUMN() instead of row, like

instead of
... ,ROW($A$1:$I$2)-MIN(ROW(1:1))+1, ...

try

... , COLUMN($A$1:$I$2)-MIN(COLUMN($A$1:$I$2))+1

 

[manc]

pgc01, sometimes the obvious is just staring right at you!

Thank you for your time and answer.

Best Regards
manc

 

[pgc01]

You're welcome.

 

[manc]

Hi everyone,

How would i now copy the above formula on the next row (and the next, and the next, etc), with row 1 remaining the "header" row, so to speak?
The formula i am using in J2 is:
=IFERROR(INDEX($A$1:$I$1,SMALL(IF($A$1:$I2=TRUE,COLUMN($A$1:$I2)-MIN(COLUMN($A$1:$I2))+1),COLUMNS($A:A))),""))

I thought i may just be able to drag it down and it would work, but it duplicates the values from row 2, displays them on row 3, THEN adds results from row 3!
Formula on row 2 reads:
=IFERROR(INDEX($A$1:$I$1,SMALL(IF($A$1:$I3=TRUE,COLUMN($A$1:$I3)-MIN(COLUMN($A$1:$I3))+1),COLUMNS($A:A))),""))

Any ideas?

Best regards
manc

 

[pgc01]

Hi again

Try in J2:

=IFERROR(INDEX($A$1:$I$1,SMALL(IF($A2:$I2=TRUE,COLUMN($A2:$I2)-MIN(COLUMN($A2:$I2))+1),COLUMNS($A:A))),"")

Copy down and across

 

[manc]

Oh you beauty pgc01. I'd give you a right big kiss if you were here!

Thank-you, thank-you, thank-you.

Best regards
manc

 

[pgc01]

I'm glad it helped. Cheers!