Combine Lists with different formulas?

[bet1734]

I have been working on a spreadsheet for tracking the MLB season scores. I manually enter in the date of the game (A), Away Team (B), Home Team in (C), Final Away Score (D), and Home Score in (E).

For simplicity's sake I've only used one team from here, San Francisco Giants SFG.
I have managed to use the Match function to have the sheet pull the row for each time a team plays whether they are away (H) or home (I).

From there, I have the Index function pull the date of the game (K), the teams score when they are away (L), their opponents score (M), and then calculate if they won or lost (N).
Separate table created to calculate the same thing when the team is home (P-S)

My question now, is there a way to combine these different calculations / tables into one list that will continue the calculations as new games are added?

San Francisco has played 5 games so far, 4 away and 1 at home.
I would like to combine these two lists (K-N & P-S) so it continues to pull the row, date, and scores but so I can have it in one table (U-X) so I can view everything by date.

If there's an easier way to do this let me know!

Baseball 2020.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	Date	Away Team	Home Team	Away Score	Home Score		Giants Game Rows				Giants Away					Giants Home					Combined Home & Away
2	23-Jul	SFG	LAD	1	8			SFG			Date	SFG	Op	W/L		Date	SFG	Op	W/L		Date	SFG	Op	W/L
3	23-Jul	NYY	WSN	4	1		1	2	67		7/23/2020	1	8	L		7/28/2020	3	5	L
4	24-Jul	BAL	BOS	2	13		2	10	#N/A		7/24/2020	1	9	L		#N/A	#N/A	#N/A	#N/A
5	24-Jul	MIN	CHW	10	5		3	24	#N/A		7/25/2020	5	4	W		#N/A	#N/A	#N/A	#N/A
6	24-Jul	MIL	CHC	0	3		4	39	#N/A		7/26/2020	3	1	W		#N/A	#N/A	#N/A	#N/A
7	24-Jul	DET	CIN	1	7		5	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A
8	24-Jul	KCR	CLE	0	2		6	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A
9	24-Jul	SEA	HOU	2	8		7	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A
10	24-Jul	SFG	LAD	1	9		8	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A
Scores

Cell Formulas
Range	Formula
H3	H3	=MATCH($H$2,OFFSET($B$1,F2,0,200,1),0)+F2
I3	I3	=MATCH($H$2,OFFSET($C$1,G2,0,200,1),0)+G2
H4:H10	H4	=MATCH($H$2,OFFSET($B$1,H3,0,200,1),0)+H3
I4:I10	I4	=MATCH($H$2,OFFSET($C$1,I3,0,200,1),0)+I3
K3:K10	K3	=INDEX(MLB[Date],H3-1)
L3:L10	L3	=INDEX(MLB[Away Score],H3-1)
M3:M10	M3	=INDEX(MLB[Home Score],H3-1)
S3:S10,N3:N10	N3	=IF(L3>M3,"W","L")
P3:P10	P3	=INDEX(MLB[Date],I3-1)
Q3:Q10	Q3	=INDEX(MLB[Home Score],I3-1)
R3:R10	R3	=INDEX(MLB[Away Score],I3-1)

 

[jasonb75]

Is this what you're looking for?
I doubt that it will ever happen, but the method I've used will give incorrect results if a team plays twice on the same date.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Away Team	Home Team	Away Score	Home Score		Combined Home & Away
2	7/23/20	SFG	LAD	1	8		Date	SFG	Op	W/L
3	7/23/20	NYY	WSN	4	1		7/23/20	1	8	L
4	7/24/20	BAL	BOS	2	13		7/24/20	1	9	L
5	7/24/20	MIN	CHW	10	5
6	7/24/20	MIL	CHC	0	3
7	7/24/20	DET	CIN	1	7
8	7/24/20	KCR	CLE	0	2
9	7/24/20	SEA	HOU	2	8
10	7/24/20	SFG	LAD	1	9
Sheet7

Cell Formulas
Range	Formula
G3:G7	G3	=IFERROR(AGGREGATE(15,6,$A$2:$A$10/($B$2:$C$10=$H$2),ROWS(G$3:G3)),"")
H3:H7	H3	=IF(G3="","",SUMIFS($D$2:$D$10,$A$2:$A$10,G3,$B$2:$B$10,$H$2)+SUMIFS($E$2:$E$10,$A$2:$A$10,G3,$C$2:$C$10,$H$2))
I3:I7	I3	=IF(G3="","",SUMIFS($E$2:$E$10,$A$2:$A$10,G3,$B$2:$B$10,$H$2)+SUMIFS($D$2:$D$10,$A$2:$A$10,G3,$C$2:$C$10,$H$2))
J3:J7	J3	=IF(I3="","",IF(H3>I3,"W","L"))

 

[Eric W]

Here's another way:

Book1
	A	B	C	D	E	F															U	V	W	X	Y
1	Date	Away Team	Home Team	Away Score	Home Score																Combined Home & Away
2	7/23/2020	SFG	LAD	1	8																Date	SFG	Op	W/L	Row
3	7/23/2020	NYY	WSN	4	1																7/23/2020	1	8	L	1
4	7/24/2020	BAL	BOS	2	13																7/24/2020	1	9	L	9
5	7/24/2020	MIN	CHW	10	5																7/25/2020	5	3	W	10
6	7/24/2020	MIL	CHC	0	3																7/25/2020	7	4	W	11
7	7/24/2020	DET	CIN	1	7
8	7/24/2020	KCR	CLE	0	2
9	7/24/2020	SEA	HOU	2	8
10	7/24/2020	SFG	LAD	1	9
11	7/25/2020	ABC	SFG	3	5
12	7/25/2020	ABC	SFG	4	7
Sheet5

Cell Formulas
Range	Formula
U3:U9	U3	=IF(Y3="","",INDEX(MLB[Date],Y3))
V3:V9	V3	=IF(Y3="","",IF(INDEX(MLB[Away Team],Y3)=$V$2,INDEX(MLB[Away Score],1),INDEX(MLB[Home Score],Y3)))
W3:W9	W3	=IF(Y3="","",SUM(INDEX(MLB[[Away Score]:[Home Score]],Y3,0))-V3)
X3:X9	X3	=IF(Y3="","",IF(V3>W3,"W","L"))
Y3:Y9	Y3	=IFERROR(AGGREGATE(15,6,(ROW(MLB[Date])-ROW(INDEX(MLB[Date],1))+1)/((MLB[Away Team]=$V$2)+(MLB[Home Team]=$V$2)),ROWS($Y$2:$Y2)),"")

I added the Row column, which you can hide. The advantage of this version is that it handles double-headers.

 

[bet1734]

Looks like this will work! Thank you both for the help!

 

[Eric W]

Oops, looks like I had a typo in the V formula, it should be:

=IF(Y3="","",IF(INDEX(MLB[Away Team],Y3)=$V$2,INDEX(MLB[Away Score],Y3),INDEX(MLB[Home Score],Y3)))

Anyway, glad we could help!