I've got a large table of school pupil data (tbl_Pupils) which includes a Pupil ID (text string) and Class name (also text).

On a new sheet I'd like to use a formula to a list Pupil IDs in row 2 from the table based on a class name entered into cell A1. The number of pupils in each class varies.

I've been trying to make use of the formula below to extract the list of pupil IDs in a helper column and then use a transpose formula to copy it to a row:

=INDEX(tbl_Pupils[Pupil ID],MATCH(A1,tbl_Pupils[Class],0),1):INDEX(tbl_Pupils[Pupil ID],MATCH(A1,tbl_Pupils[Class],1),1)

...but although the above formula works for a data-validation drop-down list I can't get it to generate a helper list.

...Could anybody point out what I'm doing wrong or suggest an alternative approach.

Many thanks