Can you reference a table name in a formula based on the value in another cell?

Question

I'm creating a workbook to track stats for a hockey team. On the first sheet I have all of the teams listed along with their players. Each team is a table and the table name is the name of the team

On the second sheet when I enter the team name I want a formula to bring over all of the players for that team based on the team name I entered

This is the formula I'm using for I6

I've tried using INDIRECT, which worked for the INDEX but not for MATCH

Answer 1

The cleanest way I could come up with for this uses a let statement to avoid copying code (assuming teams lists are on Sheet1). It also depends on your version of excel supporting dynamic arrays.

Players: =LET(cols, XMATCH($C$3, Sheet1!$B$2:$J$2), target_range, INDIRECT("Sheet1!R6C" & cols+1 & ":R1000C" & cols+1, FALSE), valid_rows, NOT(ISBLANK(target_range)), FILTER(target_range, valid_rows))

Numbers: =LET(cols, XMATCH($C$3, Sheet1!$B$2:$J$2), target_range, INDIRECT("Sheet1!R6C" & cols+1 & ":R1000C" & cols+1, FALSE), valid_rows, NOT(ISBLANK(target_range)), FILTER(OFFSET(target_range, 0, 2), valid_rows))

What it does:

cols: finds the team name (from $C$3) in the row with all the team names.

target_range: grabs rows 6-1000 in the selected column (the bound can be changed as needed). The cols+1 is needed because the data on Sheet1 starts in column B.

valid_rows: returns which values in the target_range contain player names.

Output: filters target_range to just the required values and returns a dynamic array.

The formula for returning the player numbers performs all the checks on the play name range, then pulls the array from 2 rows over.