@sf49ers19238597 I think you want to know how the formula works:
=LET(l,SUBSTITUTE(
FILTER(FullSchedule[WEEK]&" "
&FullSchedule[ROAD TEAM]&FullSchedule[HOME TEAM],
(FulSchedule[ROAD TEAM]=$A$2)
+(FullSchedule[HOME TEAM]=$A$2))
,$A$2,""),
w,TRIM(LEFT(l,3)),
t,TRIM(MID(l,3,99)),
f,XLOOKUP(SEQUENCE(18),--w,t,"BYE WEEK"),
f)
So line 1 sets up the LET() statement and 1st variable "l" Uses FILTER to create a list of rows that has this sheet's Team Name (cell A2). The list is in the format of [Week#]____[HomeTeam][RoadTeam]. So when the SUBSTITUTE replaces the [HOME TEAM] name with blank ("") it leaves a list of [Week#]____[OtherTeam]
Line 7 creates a variable "w" and strips the week#s from the list created in "l". It simply takes the first 3 (LEFT) characters and then TRIMs off any extra space.
Line 8 creates "t" similar to the week number but strips off the Opponent's team name
Line 9 creates "f" which does a lookup of every week number (1-18) in the list of week numbers "w" and returns the corresponding opponent's team name ("t") and for week number(s) that don't exist it returns "BYE WEEK".
Line 10 just say to return "f" as the final list
The Home/Away list:
=LET(li,FILTER('YEARLY SCHEDULE'!A1:B273,
('YEARLY SCHEDULE'!B1:B273=$A$2)+('YEARLY SCHEDULE'!D1:D273=$A$2)),
SWITCH(XLOOKUP(SEQUENCE(18),INDEX(li,,1),INDEX(li,,2),""),
"","",
$A$2,"AWAY",
"HOME")
)
in line 1 is the LET and 1st variable "li" which is a 2 column list with 1st column the week # and the 2nd column the HOME TEAM of each row that has this sheet's team name (as either home or road).
line 3 returns the list based on SWITCH. Switch is like a special multiple IF statement. It uses a value in this case found using XLOOKUP to determine which output to give. The XLOOKUP checks week#s 1-18 and returns the corresponding HOME TEAM from the list created in "li" and if that week# isn't found it returns blank (""). The SWITCH then says if the Value is "" then return "", if it is this sheet's team name (A2) then return "AWAY" and if anything else the return "HOME".
