Yep, another array question
I am sorry for rehashing this tired topic. I have been trying to simplify creation of multiple score sheets by referencing a table that contains all of my data and creating new tables. I have a method that works but since I am dealing with over 50 teams of five members each, the formulas are getting ridiculously large and any modifications are excruciatingly painful. I really want to be able to keep it small but simple.
This is a sample of my main data table/sheet:
Based on the team number in "Table 1" I need to automagically add the proper "Class" and "Name" from table "Roster" that match the "Team" number. Most everything I have tried will only give me the first item that matches the team number from "Roster" so I am left with this:
when what I really want is this:
I have tried the INDEX() function multiple ways with no resolution and I believe it's because I cannot get the data out of "Roster" into an array. I experimented with UNION.RANGES() and this will work with INDEX() but then I have to change the INDEX "row-index" manually for each "Name" and "Class" (I also cannot figure out how to get UNION.RANGES() to make the proper selection based on the "Team" column).
The order of the data in "Roster" cannot be changed so no sorting can be done.
My score sheet would then be a template that I could paste in any sheet and the only item I would need to change is the "Team" number to get it to update.
Any help that anyone could offer would be greatly appreciated.
Thank you!
MacBook Pro 13″, macOS 14.4