formula to return next valid cell

You can use an array formula, entered using Ctrl-Shift-Enter:

Formula:

=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$1:$A$100=$B$5,ROW($A$1:$A$100)),ROW(A1)))

then copied down until it returns blanks.