Array depending on 2 variables

[hond70]

I have the following sheet https://1drv.ms/x/s!Ai6fM6lT5gt6h0vP6kgG5MmfsdsU

It consists of a table that is populated by a list (with an array). The last column of the table is based on a value in B21.

Goal: I want to populate a second table based on the value in B21 and based on the last column of the table:

1. When there is only the value "red" in last column of the table: copy table 1 to table 2 (value of B21 doesn't matter)
2. When there is a mix of the values "red" and "green": filter on the value of B21

Any help is greatly appreciated!

 

[Bo_Ry]

Please try at C15:F18

=INDEX(C$3:C$12,IF(COUNTIF($F$3:$F$12,$B$21),AGGREGATE(15,6,ROW(C$3:C$12)-ROW(C$2)/($F$3:$F$12=$B$21),ROWS(C$15:C15)),ROWS(C$15:C15)))

 

[hond70]

Thanks, but I cannot enter the array. Is something wrong with it? Could you please try in the above mentioned sheet? See C31

 

[Bo_Ry]

Done, please check.

 

[hond70]

You did it without an array!
Unfortunately it collapses when I changed F5 to green (same situation as scenario 3). At the end of the formula it says ROWS(I$28:I29). That can't be right, does it?

 

[hond70]

I did it myself: =IFERROR(IF($AD$13=0;INDEX($AA$3:$AA$12; SMALL(IF($AD$3:$AD$12="red";ROW($AD$3:$AD$12)-ROW($AD$3)+1);ROWS($AD$3:$AD3)));INDEX($AA$3:$AA$12; SMALL(IF($AD$3:$AD$12=$AC$27;ROW($AD$3:$AD$12)-ROW($AD$3)+1);ROWS($AD$3:$AD3))));"")
Where AD13 =COUNTIF(AD3:AD12;"green")