Fetch heads up stats between 2 teams

[AYouQueTai]

Hi there,

I have the following formula that fetches a teams last 10 games (whether home or away) and it all I have to do is change the @INDEX(Results[GameID] range at the start of the formula to get the date, visitor team, vis goals, home team, home goals etc...This formula works great. (A30:F30) in "Over Under" sheet.
=IFERROR(@INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")

I have tried to modify it to fetch the "season series" of games between 2 specific teams (games played in both cities so both teams can appear in the home or visitors range) but haven't been able to figure it out.
The Over Under sheet has the visiting team in A8 and the home team in N8. I would like to fill the A23:F26 with the data from the results sheet.

Can anyone help?
Thanks,

NHL 2023-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Game ID	020884
2	Date & Time	Wednesday	Feb-21	8:00 PM
3	Over/Under	Proline Stadium					Pinnacle.com
4		Over	O%	Line	U%	Under	Over	O%	Line	U%	Under
5		1.880	53.2%	6.5	52.1%	1.920	1.917	52.2%	6.5	51.0%	1.961
6
7	Visitor Team									Score				Home Team									Score
8	Buffalo									0				Montreal									0
9
10
11	Days of Rest	1												Days of Rest	3
12	Trip													Trip
13
14	League	Games	Over	Over %	Push	Push %	Under	Under %
15	6.5	875	414	47.3%	0	0.0%	461	52.7%
16
17	Teams	Games	Over	Over %	Push	Push %	Under	Under %
18	Buffalo	26	13	50.0%	0	0.0%	13	50.0%
19	Montreal	29	16	55.2%	0	0.0%	13	44.8%
20
21	Season Series
22	Game ID	Date	Visitor	Vtot	Home	Htot	OU Result
23
24
25
26
27
28	Buffalo - Last 10
29	Game ID	Date	Visitor	Vgoal	Home	Hgoal	Vresult
30	020866	Feb-19	Anaheim	4	Buffalo	3	Over
31	020854	Feb-17	Buffalo	3	Minnesota	2	Under
32	020838	Feb-15	Florida	4	Buffalo	0	Under
33	020824	Feb-13	Los Angeles	0	Buffalo	7	Over
34	020804	Feb-10	St-Louis	3	Buffalo	1	Under
35	020784	Feb-06	Dallas	2	Buffalo	1	Under
36	020762	Jan-27	Buffalo	5	San Jose	2	Over
37	020745	Jan-24	Buffalo	5	Los Angeles	3	Over
38	020737	Jan-23	Buffalo	2	Anaheim	4	Under
39	020706	Jan-20	Tampa Bay	3	Buffalo	1	Under
OverUnder

Cell Formulas
Range	Formula
B2	B2	=INDEX(Schedule,MATCH($B$1,Schedule[GameID],0),3)
C2	C2	=INDEX(Schedule,MATCH($B$1,Schedule[GameID],0),4)
D2	D2	=INDEX(Schedule,MATCH($B$1,Schedule[GameID],0),5)
B5	B5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),7)
C5,H5	C5	=1/B5
D5	D5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),8)
E5,J5	E5	=1/F5
F5	F5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),9)
G5	G5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),12)
I5	I5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),13)
K5	K5	=INDEX(Odds,MATCH($B$1,Odds[GameID],0),14)
A8	A8	=INDEX(Results,MATCH($B$1,Results[GameID],0),6)
J8	J8	=INDEX(Results,MATCH($B$1,Results[GameID],0),7)
N8	N8	=INDEX(Results,MATCH($B$1,Results[GameID],0),15)
W8	W8	=INDEX(Results,MATCH($B$1,Results[GameID],0),16)
B11	B11	=INDEX(Schedule,MATCH($B$1,Schedule[GameID],0),8)
O11	O11	=INDEX(Schedule,MATCH($B$1,Schedule[GameID],0),12)
A15	A15	=I5
B15	B15	=SUMPRODUCT((Schedule[Status]="Completed")*1)
C15	C15	=SUMPRODUCT((Results[Status]="Completed")*(Results[GaTo]>[@League]))
D15,D18:D19	D15	=[@Over]/[@Games]
E15	E15	=SUMPRODUCT((Results[Status]="Completed")*(Results[GaTo]=[@League]))
F15,F18:F19	F15	=[@Push]/[@Games]
G15	G15	=SUMPRODUCT((Results[Status]="Completed")*(Results[GaTo]<[@League]))
H15,H18:H19	H15	=[@Under]/[@Games]
A18	A18	=A8
B18	B18	=SUMPRODUCT((Results[Status]="Completed")*(Results[Visitor]=[@Teams]))
C18	C18	=SUMPRODUCT((Results[Status]="Completed")*(Results[Visitor]=[@Teams])*(Results[GaTo]>$D$5))
E18	E18	=SUMPRODUCT((Results[Status]="Completed")*(Results[Visitor]=[@Teams])*(Results[GaTo]=$D$5))
G18	G18	=SUMPRODUCT((Results[Status]="Completed")*(Results[Visitor]=[@Teams])*(Results[GaTo]<$D$5))
A19	A19	=N8
B19	B19	=SUMPRODUCT((Results[Status]="Completed")*(Results[Home]=[@Teams]))
C19	C19	=SUMPRODUCT((Results[Status]="Completed")*(Results[Home]=[@Teams])*(Results[GaTo]>$D$5))
E19	E19	=SUMPRODUCT((Results[Status]="Completed")*(Results[Home]=[@Teams])*(Results[GaTo]=$D$5))
G19	G19	=SUMPRODUCT((Results[Status]="Completed")*(Results[Home]=[@Teams])*(Results[GaTo]<$D$5))
A28	A28	=CONCATENATE($A$8," - Last 10")
A30	A30	=IFERROR(@INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
B30:B39	B30	=IFERROR(@INDEX(Results[Date],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
C30:C39	C30	=IFERROR(@INDEX(Results[Visitor],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
D30:D39	D30	=IFERROR(@INDEX(Results[VTot],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
E30:E39	E30	=IFERROR(@INDEX(Results[Home],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
F30:F39	F30	=IFERROR(@INDEX(Results[HTot],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A30))),"")
G30:G39	G30	=IF(SUM(D30,F30)>$D$5,"Over",IF(SUM(D30,F30)<$D$5,"Under","Push"))
A31:A39	A31	=IFERROR(INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A$30:$A31))),"")

 

[AYouQueTai]

NHL 2023-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	GameID	Status	Day	Date	V	Visitor	VTot	VFG	V1	V2	V3	VOT	VSO	H	Home	HTot	HFG	H1	H2	H3	HOT	HSO	GaTi	GaTo
2	020001	Completed	Tuesday	Oct-10		Nashville	3	0	0	1	2	0	0		Tampa Bay	5	1	1	0	4	0	0		8
3	020002	Completed	Tuesday	Oct-10		Chicago	4	0	0	1	3	0	0		Pittsburgh	2	1	1	1	0	0	0		6
4	020003	Completed	Tuesday	Oct-10		Seattle	1	0	0	1	0	0	0		Vegas	4	1	2	1	1	0	0		5
5	020004	Completed	Wednesday	Oct-11		Ottawa	3	1	1	0	2	0	0		Carolina	5	0	0	2	3	0	0		8
6	020005	Completed	Wednesday	Oct-11		Montreal	5	1	1	1	3	0	0		Toronto	6	0	0	3	2	0	1	SO	11
7	020006	Completed	Wednesday	Oct-11		Chicago	1	1	1	0	0	0	0		Boston	3	0	1	1	1	0	0		4
8	020007	Completed	Wednesday	Oct-11		Winnipeg	3	0	1	1	1	0	0		Calgary	5	1	2	1	2	0	0		8
9	020008	Completed	Wednesday	Oct-11		Colorado	5	1	1	2	2	0	0		Los Angeles	2	0	0	2	0	0	0		7
10	020009	Completed	Wednesday	Oct-11		Edmonton	1	0	0	1	0	0	0		Vancouver	8	1	2	3	3	0	0		9
11	020010	Completed	Thursday	Oct-12		NY Rangers	5	1	2	1	2	0	0		Buffalo	1	0	0	1	0	0	0		6
Results

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(Schedule,MATCH([@GameID],Schedule[GameID],0),2)
C2:C11	C2	=INDEX(Schedule,MATCH([@GameID],Schedule[GameID],0),3)
D2:D11	D2	=INDEX(Schedule,MATCH([@GameID],Schedule[GameID],0),4)
F2:F11	F2	=INDEX(Schedule,MATCH([@GameID],Schedule[GameID],0),9)
G2:G11	G2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[GF Team]=[@Visitor]))
H2:H11	H2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Goal '#]=1)*(Goals[GF Team]=[@Visitor]))
I2:I11	I2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Period]=1)*(Goals[GF Team]=[@Visitor]))
J2:J11	J2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Period]=2)*(Goals[GF Team]=[@Visitor]))
K2:K11	K2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Period]=3)*(Goals[GF Team]=[@Visitor]))
L2:L11	L2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Period]="OT")*(Goals[GF Team]=[@Visitor]))
M2:M11	M2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Visitor]=[@Visitor])*(Goals[Period]="SO")*(Goals[GF Team]=[@Visitor]))
O2:O11	O2	=INDEX(Schedule,MATCH([@GameID],Schedule[GameID],0),13)
P2:P11	P2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[GF Team]=[@Home]))
Q2:Q11	Q2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Goal '#]=1)*(Goals[GF Team]=[@Home]))
R2:R11	R2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Period]=1)*(Goals[GF Team]=[@Home]))
S2:S11	S2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Period]=2)*(Goals[GF Team]=[@Home]))
T2:T11	T2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Period]=3)*(Goals[GF Team]=[@Home]))
U2:U11	U2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Period]="OT")*(Goals[GF Team]=[@Home]))
V2:V11	V2	=SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Home]=[@Home])*(Goals[Period]="SO")*(Goals[GF Team]=[@Home]))
W2:W11	W2	=IF(AND(SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Period]="OT"))=0,SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Period]="SO"))=0),"",IF(SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Period]="OT"))=1,"OT",IF(SUMPRODUCT((Goals[GameID]=[@GameID])*(Goals[Period]="SO"))=1,"SO")))
X2:X11	X2	=SUM([@VTot],[@HTot])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E889	Expression	=$G2<$P2	text	NO
E2:E889	Expression	=$G2>$P2	text	NO
N2:N889	Expression	=$P2<$G2	text	NO
N2:N889	Expression	=$P2>$G2	text	NO

 

[AYouQueTai]

Oh, nevermind I got it. Thanks anyway
=IFERROR(@INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$F$2)+1)/(Results[Status]="Completed")/(Results[Date]<$C$2)/(($A$8=Results[Visitor])+($A$8=Results[Home]))/(($N$8=Results[Visitor])+($N$8=Results[Home])),ROWS($A$30:$A30))),"")