CODEWITHSUNDEEP
arraysexcelindexingconditional-statements
C Bakker
C Bakker

Reputation: 13

Multiple Conditions for Array Formula

I'm looking to use an array formula to makes lists of students who have entered different events, but also need to split by gender.

I have the first array working where it lists the students in each event but can't seem to get the second condition to work properly.

img

Here is my working formula for the first event lists:
=IFERROR(INDEX($A$3:$A$502,SMALL(IF($C$3:$C$502=1,ROW($C$3:$C$502)-ROW($C$3)+1),ROWS($C$3:$C3))),"")

Here is my non-working attempt at the second one:
=IFERROR(INDEX($A$3:$A$502,SMALL(IF(AND($C$3:$C$15=1,$B$3:$B$15="F"),ROW($C$3:$C$15)-ROW($C$3)+1),ROWS($C$3:$C3))),"")

What am I doing wrong?

Upvotes: 1

Views: 1232

Answers (2)

user4039065
user4039065

Reputation:

Try,

=IFERROR(INDEX($A:$A, aggregate(15, 6, row($3:$502)/(($C$3:$C$502=1)*($B$3:$B$502="F")), row(1:1))), text(,))

I have no idea why your sample formula had the criteria ranges going down to row 15; the ranges need to be the same.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

Array formula do not like AND or OR use * and + respectively.

=IFERROR(INDEX($A:$A,SMALL(IF(($C$3:$C$15=1)*($B$3:$B$15="F")),ROW($C$3:$C$15)),ROW($A1))),"")

Upvotes: 2

Related Questions