Pull a row from an Excel survey into another sheet by a given variable

Welcome to the forum.

Enter this array formula in B2 (enter with Ctrl + Shift + Enter) on each worksheet and fill across and down as far as required.

Formula:

=IFERROR(INDEX(Main!B$2:B$100,SMALL(IF(Main!$A$2:$A$100=$A$1,ROW(Main!$A$2:$A$100)-MIN(ROW(Main!$A$2:$A$100))+1),ROWS($1:1))),"")

I have only addressed 100 rows of the main worksheet. If you need more, increase each instance of 100 in the above formula to what you really need.
Second file doesn't use array formulae