Selection array

[hond70]

Hi,

I have a table that is dynamically created.

........S.............T............U...........V
3......apple........45.........24..........red
4......pear..........10.........26.........red
...
12.....banana.....13.........23.........red

Column V can be either red or green.

A second table has to show only the rows where V = green
But when there is row where V is green (like in the example above), then it has to show all the rows

I now use the following array
{=IFERROR(INDEX($S$3:$S$12; SMALL(IF(COUNTIF($U$26; $V$3:$V$12); ROW($S$3:$S$12)-MIN(ROW($S$3:$S$12))+1); ROW(A1)); COLUMN(A1));"")}

U26 contains either red of green

The array works when V contains red and green rows. It returns an empty cell if U26 contains green and the first table does not have a row with green.
So if U26 = green then in the example above the formula doesn't work. If U26 = red it returns all the cells (like it should).

 

[hond70]

I may have to explain myself better.

Scenario 1:

........S.............T............U...........V
3......apple........45.........24..........red
4......pear..........10.........26.........red
...
12.....banana.....13.........23.........red

Scenario 2:

........S.............T............U...........V
3......apple........45.........24..........green
4......pear..........10.........26..........green
...
12.....banana.....13.........23.........red

U26 can be green or red.
When there is no green in column V (scenario 1) then the second table should be a copy of the first table (no filter)
When there is 1 or more green in column V (scenario 2) then the second table should only show the green rows (row 1 and 2)

 

[hond70]

See: https://1drv.ms/x/s!Ai6fM6lT5gt6h0vP6kgG5MmfsdsU

 

[hond70]

I figured it out myself...