You should have given the expected results (example)
If I understood you well, try the following:
Set the following formulas on Sheet2
In the 'D1' cell copy formula below (change range if you need)
In the 'A3' cell copy ARRAY formula below (formula finished with CSE, copy down)
Code:
=IF($G$3<>"",IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF((Sheet1!$B$2:$B$29=$G$2)*(--(YEAR(Sheet1!$C$2:$C$29)=$G$3)),ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(A1)),""),IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF(Sheet1!$B$2:$B$29=$G$2,ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(A1)),""))
Copy formula to 'B' column and down or
In the 'B3' cell copy ARRAY formula below (formula finished with CSE, copy down)
Code:
=IF($G$3<>"",IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF((Sheet1!$B$2:$B$29=$G$2)*(--(YEAR(Sheet1!$C$2:$C$29)=$G$3)),ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(B1)),""),IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF(Sheet1!$B$2:$B$29=$G$2,ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(B1)),""))
In the 'C3' cell copy ARRAY formula below (formula finished with CSE, copy down)
Code:
=IF($G$3<>"",IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF((Sheet1!$B$2:$B$29=$G$2)*(--(YEAR(Sheet1!$C$2:$C$29)=$G$3)),ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(D1)),""),IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF(Sheet1!$B$2:$B$29=$G$2,ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(D1)),""))
Copy formula to 'D' column and down or
In the 'D3' cell copy ARRAY formula below (formula finished with CSE, copy down)
Code:
=IF($G$3<>"",IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF((Sheet1!$B$2:$B$29=$G$2)*(--(YEAR(Sheet1!$C$2:$C$29)=$G$3)),ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(E1)),""),IFERROR(INDEX(Sheet1!$B$2:$F$29,SMALL(IF(Sheet1!$B$2:$B$29=$G$2,ROW(Sheet1!$B$2:$B$29)),ROW($A1:$G1))-1,COLUMN(E1)),""))
The nested COLUMN function indicates the column number.
The formula works as follows.
The base formula is an IF function consisting of two ARRAY formulas
TRUE argument - Array Formula that returns results if two criteria are active (year and number of clients)
FALSE argument - Array Formula that returns the result if one criterion is active (ie number of clients)