How to show last 5 games team's form

[VKiprijan]

Hi Everyone,

I have a soccer league table with rankings in which I would like to show each team's form in the last 5 games played (Wins, Draw, Loss). What formula do I need to use in the columns CT thru CX in order to show this kind of statistics for each team?

Thank you all in advance.

Soccer League.xlsx
	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX
3	German Bundes League
4
5			Ranking System				Total								Home								Away								Last 5 games
6	Ranking	Team	POINTS	GD	GS	GA	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P
7	9	Eintracht Frankfurt	9	0	0	0	34	11	10	10	51	49	2	43	17	7	5	3	30	17	13	26	17	4	5	7	21	32	-11	17
8	1	Bayern Munich	1	0	0	0	34	19	8	4	83	34	49	65	17	10	5	0	46	14	32	35	17	9	3	4	37	20	17	30
9	7	Wolfsburg	7	0	0	0	34	12	10	9	54	43	11	46	17	5	7	3	31	22	9	22	17	7	3	6	23	21	2	24
10	12	Werder Bremen	12	0	0	0	34	10	5	16	49	60	-11	35	17	5	1	10	25	27	-2	16	17	5	4	6	24	33	-9	19
11	4	Union Berlin	4	0	0	0	34	16	8	7	44	32	12	56	17	9	6	0	25	9	16	33	17	7	2	7	19	23	-4	23
12	18	Hertha Berlin	18	0	0	0	34	6	7	18	37	62	-25	25	17	5	5	6	23	26	-3	20	17	1	2	12	14	36	-22	5
13	17	Bochum	16	1	0	0	34	8	4	19	33	69	-36	28	17	6	2	7	22	30	-8	20	17	2	2	12	11	39	-28	8
14	8	Mainz	8	0	0	0	34	12	9	10	51	46	5	45	17	6	6	4	30	21	9	24	17	6	3	6	21	25	-4	21
15	10	B. Monchengladbach	10	0	0	0	34	10	9	12	46	48	-2	39	17	9	3	4	31	18	13	30	17	1	6	8	15	30	-15	9
16	14	Hoffenheim	14	0	0	0	34	9	5	17	42	52	-10	32	17	6	2	8	24	27	-3	20	17	3	3	9	18	25	-7	12
17	13	Augsburg	13	0	0	0	34	9	7	15	40	55	-15	34	17	6	4	6	16	21	-5	22	17	3	3	9	24	34	-10	12
18	5	Freiburg	4	1	0	0	34	16	8	7	46	38	8	56	17	9	4	3	26	13	13	31	17	7	4	4	20	25	-5	25
19	2	Dortmund	2	0	0	0	34	20	4	7	73	40	33	64	17	13	1	1	48	13	35	40	17	7	3	6	25	27	-2	24
20	6	Bayer Leverkusen	6	0	0	0	34	14	6	11	54	43	11	48	17	8	2	6	33	21	12	26	17	6	4	5	21	22	-1	22
21	16	Stuttgart	16	0	0	0	34	6	10	15	39	54	-15	28	17	5	4	6	21	20	1	19	17	1	6	9	18	34	-16	9
22	3	RB Leipzig	3	0	0	0	34	17	6	8	55	37	18	57	17	11	2	2	32	15	17	35	17	6	4	6	23	22	1	22
23	11	FC Koln	11	0	0	0	34	9	11	11	42	49	-7	38	17	5	5	5	22	16	6	20	17	4	6	6	20	33	-13	18
24	15	Schalke	15	0	0	0	34	7	9	15	31	59	-28	30	17	5	4	7	21	34	-13	19	17	2	5	8	10	25	-15	11
Bunde League

Cell Formulas
Range	Formula
BR7:BR24	BR7	=RANK(CC7,$CC$7:$CC$24)
BS7:BS24	BS7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24	BT7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24	BU7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24	BV7	=CD7+CL7
CD7:CD24	CD7	=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24	CE7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24	CF7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24	CG7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24	CH7	=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24	CI7	=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24	CJ7	=CH7-CI7
CK7:CK24,CS7:CS24	CK7	=CE7*3+CF7*1
CL7:CL24	CL7	=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24	CM7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24	CN7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($G$5:$G$346<>""))
CO7:CO24	CO7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24	CP7	=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24	CQ7	=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24	BP7	=SUM(BR7:BU7)

 

[VKiprijan]

In what way did they not work?

I believe that there was some problem with the formula in my Excel 2016 environment thus not working properly as Peter_SS pointed out.

 

[Fluff]

Peter was referring to the formula he originally posted.
If either Peter's suggestion or mine are not working you need to show what is wrong.

 

[VKiprijan]

Peter was referring to the formula he originally posted.
If either Peter's suggestion or mine are not working you need to show what is wrong.

I am currently using an Excel version that does not have XL2BB add-on so when I get at home will check and advise what is the problem. Thanks again.

 

[Fluff]

Are these the correct results.

Fluff.xlsm
	BQ																													CT	CU	CV	CW	CX
5																														Last 5 games
6	Team
7	Eintracht Frankfurt																													L	D	L	D	L
8	Bayern Munich																													W	W	L	D	W
9	Wolfsburg																													L	W	W	D	L
10	Werder Bremen																													L	L	W	L	D
11	Union Berlin																													L	D	W	D	L
12	Hertha Berlin																													W	L	L	L	L
13	Bochum																													L	D	L	D	L
14	Mainz																													L	L	W	D	D
15	B. Monchengladbach																													W	L	L	D	W
16	Hoffenheim																													W	L	L	D	W
17	Augsburg																													W	D	D	L	L
18	Freiburg																													L	W	W	W	L
19	Dortmund																													W	D	W	D	W
20	Bayer Leverkusen																													L	D	W	D	W
21	Stuttgart																													L	W	D	D	W
22	RB Leipzig																													W	W	L	W	W
23	FC Koln																													W	L	W	D	W
24	Schalke																													W	W	L	W	L
Master

Cell Formulas
Range	Formula
CT7:CX24	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))*(IF(INDEX($C:$C,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))=$BQ7,1,-1))+2,"L","D","W")

 

[VKiprijan]

Are these the correct results.

Fluff.xlsm
	BQ																													CT	CU	CV	CW	CX
5																														Last 5 games
6	Team
7	Eintracht Frankfurt																													L	D	L	D	L
8	Bayern Munich																													W	W	L	D	W
9	Wolfsburg																													L	W	W	D	L
10	Werder Bremen																													L	L	W	L	D
11	Union Berlin																													L	D	W	D	L
12	Hertha Berlin																													W	L	L	L	L
13	Bochum																													L	D	L	D	L
14	Mainz																													L	L	W	D	D
15	B. Monchengladbach																													W	L	L	D	W
16	Hoffenheim																													W	L	L	D	W
17	Augsburg																													W	D	D	L	L
18	Freiburg																													L	W	W	W	L
19	Dortmund																													W	D	W	D	W
20	Bayer Leverkusen																													L	D	W	D	W
21	Stuttgart																													L	W	D	D	W
22	RB Leipzig																													W	W	L	W	W
23	FC Koln																													W	L	W	D	W
24	Schalke																													W	W	L	W	L
Master

Cell Formulas
Range	Formula
CT7:CX24	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))*(IF(INDEX($C:$C,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))=$BQ7,1,-1))+2,"L","D","W")

Yes, these above are correct results and the formula this time works fine.


Thank you so much.

 

[VKiprijan]

How do I need to modify this formula for the last 5 games to show me the last 5 games at home and away like on this table below:

Soccer League v2.xlsx
	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
3	German Bundes League
4
5			Ranking System				Total								Home								Away								Last 5 games (home)					Last 5 games (away)
6	Ranking	Team	POINTS	GD	GS	GA	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P
7	9	Eintracht Frankfurt	9	0	0	0	34	11	10	10	51	49	2	43	17	7	5	3	30	17	13	26	17	4	5	7	21	32	-11	17
8	1	Bayern Munich	1	0	0	0	34	19	8	4	83	34	49	65	17	10	5	0	46	14	32	35	17	9	3	4	37	20	17	30
9	7	Wolfsburg	7	0	0	0	34	12	10	9	54	43	11	46	17	5	7	3	31	22	9	22	17	7	3	6	23	21	2	24
10	12	Werder Bremen	12	0	0	0	34	10	5	16	49	60	-11	35	17	5	1	10	25	27	-2	16	17	5	4	6	24	33	-9	19
11	4	Union Berlin	4	0	0	0	34	16	8	7	44	32	12	56	17	9	6	0	25	9	16	33	17	7	2	7	19	23	-4	23
12	18	Hertha Berlin	18	0	0	0	34	6	7	18	37	62	-25	25	17	5	5	6	23	26	-3	20	17	1	2	12	14	36	-22	5
13	17	Bochum	16	1	0	0	34	8	4	19	33	69	-36	28	17	6	2	7	22	30	-8	20	17	2	2	12	11	39	-28	8
14	8	Mainz	8	0	0	0	34	12	9	10	51	46	5	45	17	6	6	4	30	21	9	24	17	6	3	6	21	25	-4	21
15	10	B. Monchengladbach	10	0	0	0	34	10	9	12	46	48	-2	39	17	9	3	4	31	18	13	30	17	1	6	8	15	30	-15	9
16	14	Hoffenheim	14	0	0	0	34	9	5	17	42	52	-10	32	17	6	2	8	24	27	-3	20	17	3	3	9	18	25	-7	12
17	13	Augsburg	13	0	0	0	34	9	7	15	40	55	-15	34	17	6	4	6	16	21	-5	22	17	3	3	9	24	34	-10	12
18	5	Freiburg	4	1	0	0	34	16	8	7	46	38	8	56	17	9	4	3	26	13	13	31	17	7	4	4	20	25	-5	25
19	2	Dortmund	2	0	0	0	34	20	4	7	73	40	33	64	17	13	1	1	48	13	35	40	17	7	3	6	25	27	-2	24
20	6	Bayer Leverkusen	6	0	0	0	34	14	6	11	54	43	11	48	17	8	2	6	33	21	12	26	17	6	4	5	21	22	-1	22
21	16	Stuttgart	16	0	0	0	34	6	10	15	39	54	-15	28	17	5	4	6	21	20	1	19	17	1	6	9	18	34	-16	9
22	3	RB Leipzig	3	0	0	0	34	17	6	8	55	37	18	57	17	11	2	2	32	15	17	35	17	6	4	6	23	22	1	22
23	11	FC Koln	11	0	0	0	34	9	11	11	42	49	-7	38	17	5	5	5	22	16	6	20	17	4	6	6	20	33	-13	18
24	15	Schalke	15	0	0	0	34	7	9	15	31	59	-28	30	17	5	4	7	21	34	-13	19	17	2	5	8	10	25	-15	11
Bunde League

Cell Formulas
Range	Formula
BR7:BR24	BR7	=RANK(CC7,$CC$7:$CC$24)
BS7:BS24	BS7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24	BT7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24	BU7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24	BV7	=CD7+CL7
CD7:CD24	CD7	=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24	CE7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24	CF7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24	CG7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24	CH7	=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24	CI7	=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24	CJ7	=CH7-CI7
CK7:CK24,CS7:CS24	CK7	=CE7*3+CF7*1
CL7:CL24	CL7	=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24	CM7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24	CN7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($G$5:$G$346<>""))
CO7:CO24	CO7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24	CP7	=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24	CQ7	=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24	BP7	=SUM(BR7:BU7)

Thank you so much in advance.

 

[Fluff]

How about

Fluff.xlsm
	BP	BQ																													CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
5																															Last 5 games
6	Ranking	Team
7	9	Eintracht Frankfurt																													D	D	D	D	W	W	W	W	W	D
8	1	Bayern Munich																													W	D	W	W	W	L	W	L	W	L
9	7	Wolfsburg																													W	D	D	D	D	W	L	W	L	L
10	12	Werder Bremen																													L	L	L	L	W	W	L	D	D	W
11	4	Union Berlin																													D	D	W	W	D	W	L	W	D	W
12	18	Hertha Berlin																													W	L	L	D	W	W	W	D	W	W
13	17	Bochum																													D	L	L	W	L	W	D	D	L	W
14	8	Mainz																													L	W	D	D	W	W	D	L	D	L
15	10	B. Monchengladbach																													W	L	W	D	D	W	D	D	W	W
16	14	Hoffenheim																													W	L	W	W	L	W	D	L	W	W
17	13	Augsburg																													W	D	L	D	W	D	W	D	W	W
18	5	Freiburg																													L	W	L	D	W	L	L	D	D	L
19	2	Dortmund																													W	W	W	W	W	D	D	W	D	L
20	6	Bayer Leverkusen																													L	W	W	W	W	D	D	L	L	D
21	16	Stuttgart																													W	D	L	L	W	W	D	L	W	D
22	3	RB Leipzig																													W	W	L	W	W	L	W	L	W	W
23	11	FC Koln																													L	D	D	L	L	L	L	L	W	D
24	15	Schalke																													W	W	L	D	W	L	W	W	D	L
Master

Cell Formulas
Range	Formula
CT7:CX24	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
CY7:DC24	CY7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
BP7:BP24	BP7	=SUM(BR7:BU7)

 

[Fluff]

Ignore the previous post the away games are wrong.
Use

Fluff.xlsm
	BP	BQ																													CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
5																															Last 5 games
6	Ranking	Team
7	9	Eintracht Frankfurt																													D	D	D	D	W	L	L	L	L	D
8	1	Bayern Munich																													W	D	W	W	W	W	L	W	L	W
9	7	Wolfsburg																													W	D	D	D	D	L	W	L	W	W
10	12	Werder Bremen																													L	L	L	L	W	L	W	D	D	L
11	4	Union Berlin																													D	D	W	W	D	L	W	L	D	L
12	18	Hertha Berlin																													W	L	L	D	W	L	L	D	L	L
13	17	Bochum																													D	L	L	W	L	L	D	D	W	L
14	8	Mainz																													L	W	D	D	W	L	D	W	D	W
15	10	B. Monchengladbach																													W	L	W	D	D	L	D	D	L	L
16	14	Hoffenheim																													W	L	W	W	L	L	D	W	L	L
17	13	Augsburg																													W	D	L	D	W	D	L	D	L	L
18	5	Freiburg																													L	W	L	D	W	W	W	D	D	W
19	2	Dortmund																													W	W	W	W	W	D	D	L	D	W
20	6	Bayer Leverkusen																													L	W	W	W	W	D	D	W	W	D
21	16	Stuttgart																													W	D	L	L	W	L	D	W	L	D
22	3	RB Leipzig																													W	W	L	W	W	W	L	W	L	L
23	11	FC Koln																													L	D	D	L	L	W	W	W	L	D
24	15	Schalke																													W	W	L	D	W	W	L	L	D	W
Master

Cell Formulas
Range	Formula
CT7:CX24	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
CY7:DC24	CY7	=CHOOSE(SIGN(INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
BP7:BP24	BP7	=SUM(BR7:BU7)

 

[VKiprijan]

How about

Fluff.xlsm
	BP	BQ																													CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
5																															Last 5 games
6	Ranking	Team
7	9	Eintracht Frankfurt																													D	D	D	D	W	W	W	W	W	D
8	1	Bayern Munich																													W	D	W	W	W	L	W	L	W	L
9	7	Wolfsburg																													W	D	D	D	D	W	L	W	L	L
10	12	Werder Bremen																													L	L	L	L	W	W	L	D	D	W
11	4	Union Berlin																													D	D	W	W	D	W	L	W	D	W
12	18	Hertha Berlin																													W	L	L	D	W	W	W	D	W	W
13	17	Bochum																													D	L	L	W	L	W	D	D	L	W
14	8	Mainz																													L	W	D	D	W	W	D	L	D	L
15	10	B. Monchengladbach																													W	L	W	D	D	W	D	D	W	W
16	14	Hoffenheim																													W	L	W	W	L	W	D	L	W	W
17	13	Augsburg																													W	D	L	D	W	D	W	D	W	W
18	5	Freiburg																													L	W	L	D	W	L	L	D	D	L
19	2	Dortmund																													W	W	W	W	W	D	D	W	D	L
20	6	Bayer Leverkusen																													L	W	W	W	W	D	D	L	L	D
21	16	Stuttgart																													W	D	L	L	W	W	D	L	W	D
22	3	RB Leipzig																													W	W	L	W	W	L	W	L	W	W
23	11	FC Koln																													L	D	D	L	L	L	L	L	W	D
24	15	Schalke																													W	W	L	D	W	L	W	W	D	L
Master

Cell Formulas
Range	Formula
CT7:CX24	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
CY7:DC24	CY7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
BP7:BP24	BP7	=SUM(BR7:BU7)

The formula for the home games works fine but for the away games does not work properly. Here is the manual check for the first three teams where highlighted in red are the wrong ones.

Soccer League v2.xlsx
	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
5	Last 5 games (home)					Last 5 games (away)
6
7	D	D	D	D	W	W	W	W	W	D
8	W	D	W	W	W	L	W	L	W	L
9	W	D	D	D	D	W	L	W	L	L
Bunde League

Cell Formulas
Range	Formula
CT7:CX9	CT7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($C$5:$C$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")
CY7:DC9	CY7	=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/($H$5:$H$500=$BQ7)/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))+2,"L","D","W")

Thanks.

 

[Fluff]

See post#28, I already realised the error.