Formula to find A49 value throughout multiple tables names and return the values of the table

I've been working for a total of 20 hours on an excel project and I've come to an impass.

Worksheet1 has a lots of query tables, all named after a team and its content.

Exemple :

Team1_goalie_profile, Team1_goalie_stats, Team1_forwards_profile, Team1_forward_stats, Team1_defense_profile, Team1_defense_stats, Team1_staff. This goes on for 17 different teams for a total of 119 tables.

All the profiles and staff are grouped in one sheet title LEAGUE_profile, stats are in one called LEAGUE_stats.

Worksheet2 has the roster sheet. Upper half the sheet is our local team (not included in the 17 teams...) and this part is fine. The second half should be the visiting team. The visiting team's name is in A49 (with a formula, if that matters. It works.). However, i cannot find a formula to fill the visiting roster up.

I'll try to be clear : the values will all be included in these columns : [D,E,F,G,H,I,J,K] and [Q] comes from the profile query, [L,M,N,O,P] comes from the stats query.

Goalie section are rows 42-43-44

Forwards, row : 46 to 61

Defense, row : 63 to 73

Staff, row : 74 to 78

I've tried XLOOKUP, but I must no be doing correctly since it comes back with #VALUE!. To start up, I went for the goalies, which the queries are in row 2 to 5 (with headers). The only formula I came up with (and ultimatly didn't worked) was to following :

=XLOOKUP($A$49, [LEAGUE_profil]$A$2:$FP$5,[LEAGUE_profile]$A$3:$FP$5,"",-1)

I've done some research but I'm really struggling. The main goal I want to reach is the following :

The Formula finds the tables with A49 in its name and return the table's data.

I've seen the formulas INDIRECT, XLOOKUP, INDEX, MATCH, SCAN thrown around but I don't know where to begin or try. If someone can enlighten me, I'd appreciate it a lot.

Thank you!

edit : My excel version is Excel 2508 and I'm canadian (I'm working with the french version...). I don't have any add-ins, because I want my collegue to be able to use the worksheets in case I'm absent.