formula which returns horizantal range to vertical range

You would use something like:

Sheet1!A2:
=INDEX(Sheet9!$1:$1000,MATCH($A$1,Sheet!9!$A:$A,0),ROWS(A$2:A2))
copied down
(adjust range as necessary)

where Sheet1!A1 holds the criteria as found in Col A on Sheet9.

I would add though that

a) it makes sense to calculate the MATCH separately and refer to the cell containing the result in the INDEX calls (ie conduct once only)

b) if the number of columns to return varies then conduct an appropriate test to determine how many values are to be returned and use that value via a pre-emptive IF to est. as to whether or not you need calculate the INDEX for a given row.

If your data in Sheet9 is in A1:K20, then perhaps

=INDEX(Sheet9!$A$1:$K$20,MATCH($A$1,Sheet9!$A$1:$A$20,0),ROW()-ROW($A$1)+1)

where $A$1 is the column header to match to row header in Sheet9

copied down after adjusting ranges.