Return Multiple Items with One Lookup Value

There are ways to do this with an extra helper "index" column in the main data table.

But first, have you considered what may be the easiest solution. In the data table, filter on AUS (or whatever), select the body cells, command-c to copy, click once in the destination table and command-v to paste?

SG

You could try something like this:

The formula in the Index column, copied down:

=B2&COUNTIF(B$1:B2,B2)

The formula in A2 of the Players From table, copied down and right:

=INDEX(Table 1::$A,MATCH(A$1&ROW()−1,Table 1::$D,0),)

To suppress the display of the red warning triangles, you can wrap that in IFERROR, like this:

=IFERROR(INDEX(Table 1::$A,MATCH(A$1&ROW()−1,Table 1::$D,0),),"")

This takes the value in row 1, appends the row number it's on less 1, and uses MATCH to find what row contains that value in the Index column of the other table. That row number is fed to INDEX, which returns the value from column A.

SG