Array formula only returns the first value

I asked the same question a while ago and get a good reply from Ron Coderre

He suggested both an array and non-array formula solution. The formulas are complex. I offer a very simple, intuitive, approach that uses simple, non-array formulas with a helper column.

Say we have data in columns A and B. In C2 enter:

=SUBTOTAL(3,$A$2:$A2)

and copy downward:

The helper column is a simple set of sequential values. We now filter for dog:

The helper has adjusted to remain a simple sequential sequence! This allows us to use a standard MATCH()/INDEX() approach to grab the visible data. In Sheet2 cell A1 enter:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!C:C,0)),"")

and copy downward:

If you can't use a helper column, try one of the Coderre formulas.