I need help with updated formula for new file I not sure how to.

[sf49ers19238597]

I need help with updated formula for new file I not sure how to. making new file  I need get opponents & away/home

49ers main worksheet

OPPONENTS
from nfl schedule

=LET(in,INDEX('Yearly Schedule'!A1:G273,SEQUENCE(273),{1,4,7}),tl,FILTER(in,(INDEX(in,,2)=$A$2)+(INDEX(in,,3)=$A$2),""),tw,INDEX(tl,,1),tt,SUBSTITUTE(INDEX(tl,,2)&INDEX(tl,,3),$A$2,""),XLOOKUP(SEQUENCE(18),tw,tt,"BYE"))

new need help update get a #ref error

from new nfl schedule

=LET(in,INDEX('YEARLY SCHEDULE'!A5:G276,SEQUENCE(276),{1,4,7}),tl,FILTER(in,(INDEX(in,,2)=$A$5)+(INDEX(in,,3)=$A$5),""),tw,INDEX(tl,,1),tt,SUBSTITUTE(INDEX(tl,,2)&INDEX(tl,,3),$A$5,""),XLOOKUP(SEQUENCE(18),tw,tt,"BYE"))

AWAY/HOME
from nfl schedule

=IF(COUNTIFS('Yearly Schedule'!A1:A273,A15:A32,'Yearly Schedule'!D1:D273,$A$2),"AWAY",IF(COUNTIFS('Yearly Schedule'!A1:A273,A15:A32,'Yearly Schedule'!G1:G273,$A$2),"HOME",""))

new

from new nfl schedule

=IF(COUNTIFS('Yearly Schedule'!A1:A273,A15:A32,'Yearly Schedule'!D1:D273,$A$5),"AWAY",IF(COUNTIFS('Yearly Schedule'!A1:A273,A15:A32,'Yearly Schedule'!G1:G273,$A$5),"HOME",""))

[#2 · Accepted solution]

@sf49ers19238597 

It looks like first formula shall be

=LET(in,INDEX('YEARLY SCHEDULE'!A5:G276,SEQUENCE(272),{1,3,6}),
  tl,FILTER(in,(INDEX(in,,2)=$A$5)+(INDEX(in,,3)=$A$5),""),
  tw,INDEX(tl,,1),tt,SUBSTITUTE(INDEX(tl,,2)&INDEX(tl,,3),$A$5,""),
  XLOOKUP(SEQUENCE(18),tw,tt,"BYE"))

another number in sequence, another columns to select and A5 instead of A2

Perhaps another one is similar

[#3]

Thanks You. I don't want see any formulas helper