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)

 

[Peter_SSs]

Not really sure why you wrote that

I think the OP wants results more like Fluff's since they asked for columns CT:CX to be filled.

What about the formula in post#7?

Those results do not seem correct to me. Perhaps I'm mis-interpreting the data but aren't the game's final result in columns F & G? If that is so, it seems to me that the last 5 results for Bayern Munich, reading from the bottom up are W,W,L,D,W

VKiprijan.xlsm
	B	C	D	E	F	G	H
240	08.04.2023	Freiburg	0	0	0	1	Bayern Munich
252	15.04.2023	Bayern Munich	1	0	1	1	Hoffenheim
258	22.04.2023	Mainz	0	1	3	1	Bayern Munich
273	30.04.2023	Bayern Munich	0	0	2	0	Hertha Berlin
282	06.05.2023	Werder Bremen	0	0	1	2	Bayern Munich
Sheet2

Edit: Looks like I missed the OP's Excel version information so the following will be no good in that version.

I'm wondering if a helper column (Col I for me) could be used to simplify the formulas considerably.
In cols CT:CX I have the most recent result in col CT. I have assumed at least 5 games exist for each team.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	005|Eintracht Frankfurt|L|Bayern Munich|W|1																																																																																									Last 5 games
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	006|Wolfsburg|D|Werder Bremen|D|1																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	007|Union Berlin|W|Hertha Berlin|L|1																																																											9	Eintracht Frankfurt																													L	D	L	D	L
8	06.08.2022	Bochum	1	1	1	2	Mainz	008|Bochum|L|Mainz|W|1																																																											1	Bayern Munich																													W	W	L	D	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	009|B. Monchengladbach|W|Hoffenheim|L|1																																																											7	Wolfsburg																													L	W	W	D	L
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	010|Augsburg|L|Freiburg|W|1																																																											12	Werder Bremen																													L	L	W	L	D
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	011|Dortmund|W|Bayer Leverkusen|L|1																																																											4	Union Berlin																													L	D	W	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	012|Stuttgart|D|RB Leipzig|D|1																																																											18	Hertha Berlin																													W	L	L	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	013|FC Koln|W|Schalke|L|1																																																											17	Bochum																													L	D	L	D	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	014|Freiburg|L|Dortmund|W|1																																																											8	Mainz																													L	L	W	D	D
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	015|Werder Bremen|D|Stuttgart|D|1																																																											10	B. Monchengladbach																													W	L	L	D	W
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	016|RB Leipzig|D|FC Koln|D|1																																																											14	Hoffenheim																													W	L	L	D	W
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	017|Hoffenheim|W|Bochum|L|1																																																											13	Augsburg																													W	D	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	018|Hertha Berlin|D|Eintracht Frankfurt|D|1																																																											5	Freiburg																													L	W	W	W	L
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	019|Bayer Leverkusen|L|Augsburg|W|1																																																											2	Dortmund																													W	D	W	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	020|Schalke|D|B. Monchengladbach|D|1																																																											6	Bayer Leverkusen																													L	D	W	D	W
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	021|Mainz|D|Union Berlin|D|1																																																											16	Stuttgart																													L	W	D	D	W
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	022|Bayern Munich|W|Wolfsburg|L|1																																																											3	RB Leipzig																													W	W	L	W	W
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	023|B. Monchengladbach|W|Hertha Berlin|L|1																																																											11	FC Koln																													W	L	W	D	W
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	024|Wolfsburg|D|Schalke|D|1																																																											15	Schalke																													W	W	L	W	L
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	025|Stuttgart|L|Freiburg|W|1
26	20.08.2022	Dortmund	1	0	2	3	Werder Bremen	026|Dortmund|L|Werder Bremen|W|1
Sheet2

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LET(r,SORT(I$5:I$500,,-1),TRANSPOSE(TEXTBEFORE(TEXTAFTER(TAKE(FILTER(r,ISNUMBER(SEARCH("|"&BQ7&"|",r))),5),"|"&BQ7&"|"),"|")))
BP7:BP24	BP7	=SUM(BR7:BU7)
I5:I26	I5	=IF(B5="","",LET(s,SIGN(F5-G5)+2,TEXTJOIN("|",,TEXT(ROW(),"000"),C5,MID("LDW",s,1),H5,MID("WDL",s,1),1)))
Dynamic array formulas.

 

[VKiprijan]

How about

Fluff.xlsm
	BP	BQ																													CT	CU	CV	CW	CX
5																															Last 5 games
6	Ranking	Team
7	9	Eintracht Frankfurt																													L	D	L	D	L
8	1	Bayern Munich																													W	L	L	D	W
9	7	Wolfsburg																													L	L	W	D	L
10	12	Werder Bremen																													L	L	W	W	D
11	4	Union Berlin																													L	D	W	D	L
12	18	Hertha Berlin																													W	L	W	L	W
13	17	Bochum																													L	D	W	D	W
14	8	Mainz																													L	L	L	D	D
15	10	B. Monchengladbach																													W	L	W	D	L
16	14	Hoffenheim																													W	L	W	D	L
17	13	Augsburg																													W	D	D	L	W
18	5	Freiburg																													L	W	L	W	W
19	2	Dortmund																													W	D	L	D	L
20	6	Bayer Leverkusen																													L	D	L	D	L
21	16	Stuttgart																													L	L	D	D	W
22	3	RB Leipzig																													W	L	L	L	W
23	11	FC Koln																													W	W	W	D	W
24	15	Schalke																													W	L	L	L	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")
BP7:BP24	BP7	=SUM(BR7:BU7)

These above formulas seemed to be working but when I manually checked the sequence of the last 5 games played and compare them with these formulas, I get incorrect results. For example, Eintracht Frankfurt's L5G form is matching with the results (L-D-L-D-L) but when I compare the L5G form of Bayern Munich (W-L-L-D-W) against the results of those 5 games I get the following W-W-L-D-W. The same thing I am getting for Wolfsburg: L5G (L-L-W-D-L) versus L-W-W-D-L. It looks that something weird happens with the second column CU where it always returns a wrong result.

 

[VKiprijan]

What about the formula in post#7?

Please see my reply #12

 

[VKiprijan]

I think the OP wants results more like Fluff's since they asked for columns CT:CX to be filled.


Those results do not seem correct to me. Perhaps I'm mis-interpreting the data but aren't the game's final result in columns F & G? If that is so, it seems to me that the last 5 results for Bayern Munich, reading from the bottom up are W,W,L,D,W

VKiprijan.xlsm
	B	C	D	E	F	G	H
240	08.04.2023	Freiburg	0	0	0	1	Bayern Munich
252	15.04.2023	Bayern Munich	1	0	1	1	Hoffenheim
258	22.04.2023	Mainz	0	1	3	1	Bayern Munich
273	30.04.2023	Bayern Munich	0	0	2	0	Hertha Berlin
282	06.05.2023	Werder Bremen	0	0	1	2	Bayern Munich
Sheet2

I'm wondering if a helper column (Col I for me) could be used to simplify the formulas considerably.
In cols CT:CX I have the most recent result in col CT. I have assumed at least 5 games exist for each team.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	005|Eintracht Frankfurt|L|Bayern Munich|W|1																																																																																									Last 5 games
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	006|Wolfsburg|D|Werder Bremen|D|1																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	007|Union Berlin|W|Hertha Berlin|L|1																																																											9	Eintracht Frankfurt																													L	D	L	D	L
8	06.08.2022	Bochum	1	1	1	2	Mainz	008|Bochum|L|Mainz|W|1																																																											1	Bayern Munich																													W	W	L	D	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	009|B. Monchengladbach|W|Hoffenheim|L|1																																																											7	Wolfsburg																													L	W	W	D	L
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	010|Augsburg|L|Freiburg|W|1																																																											12	Werder Bremen																													L	L	W	L	D
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	011|Dortmund|W|Bayer Leverkusen|L|1																																																											4	Union Berlin																													L	D	W	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	012|Stuttgart|D|RB Leipzig|D|1																																																											18	Hertha Berlin																													W	L	L	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	013|FC Koln|W|Schalke|L|1																																																											17	Bochum																													L	D	L	D	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	014|Freiburg|L|Dortmund|W|1																																																											8	Mainz																													L	L	W	D	D
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	015|Werder Bremen|D|Stuttgart|D|1																																																											10	B. Monchengladbach																													W	L	L	D	W
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	016|RB Leipzig|D|FC Koln|D|1																																																											14	Hoffenheim																													W	L	L	D	W
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	017|Hoffenheim|W|Bochum|L|1																																																											13	Augsburg																													W	D	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	018|Hertha Berlin|D|Eintracht Frankfurt|D|1																																																											5	Freiburg																													L	W	W	W	L
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	019|Bayer Leverkusen|L|Augsburg|W|1																																																											2	Dortmund																													W	D	W	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	020|Schalke|D|B. Monchengladbach|D|1																																																											6	Bayer Leverkusen																													L	D	W	D	W
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	021|Mainz|D|Union Berlin|D|1																																																											16	Stuttgart																													L	W	D	D	W
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	022|Bayern Munich|W|Wolfsburg|L|1																																																											3	RB Leipzig																													W	W	L	W	W
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	023|B. Monchengladbach|W|Hertha Berlin|L|1																																																											11	FC Koln																													W	L	W	D	W
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	024|Wolfsburg|D|Schalke|D|1																																																											15	Schalke																													W	W	L	W	L
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	025|Stuttgart|L|Freiburg|W|1
26	20.08.2022	Dortmund	1	0	2	3	Werder Bremen	026|Dortmund|L|Werder Bremen|W|1
Sheet2

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LET(r,SORT(I$5:I$500,,-1),TRANSPOSE(TEXTBEFORE(TEXTAFTER(TAKE(FILTER(r,ISNUMBER(SEARCH("|"&BQ7&"|",r))),5),"|"&BQ7&"|"),"|")))
BP7:BP24	BP7	=SUM(BR7:BU7)
I5:I26	I5	=IF(B5="","",LET(s,SIGN(F5-G5)+2,TEXTJOIN("|",,TEXT(ROW(),"000"),C5,MID("LDW",s,1),H5,MID("WDL",s,1),1)))
Dynamic array formulas.

Yes, exactly, the final results of the game are given in the F and G columns and the results of the last 5 games for Bayern Munich are W,W,L,D,W. For some reason, the second column (CU) is showing incorrect results for each of these teams.

 

[Fluff]

Oops, I missed the anchor on one of the ranges, so it should be

=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")

 

[Peter_SSs]

results of the last 5 games for Bayern Munich are W,W,L,D,W. For some reason, the second column (CU) is showing incorrect results for each of these teams.

Do you mean in my results? You listed those blue results as the correct result for Bayern Munich and that is exactly what my results showed:

Having said that, I have realised that I used functions in my formulas that you will not have in your 2016 version.

Here is an alternative, still using a helper column (which you can hide) to keep the formulas a bit shorter, that I think should work in your version.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	|Eintracht Frankfurt|L|Bayern Munich|W																																																																																									Last 5 games
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	|Wolfsburg|D|Werder Bremen|D																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	|Union Berlin|W|Hertha Berlin|L																																																											9	Eintracht Frankfurt																													L	D	L	D	L
8	06.08.2022	Bochum	1	1	1	2	Mainz	|Bochum|L|Mainz|W																																																											1	Bayern Munich																													W	W	L	D	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	|B. Monchengladbach|W|Hoffenheim|L																																																											7	Wolfsburg																													L	W	W	D	L
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	|Augsburg|L|Freiburg|W																																																											12	Werder Bremen																													L	L	W	L	D
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	|Dortmund|W|Bayer Leverkusen|L																																																											4	Union Berlin																													L	D	W	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	|Stuttgart|D|RB Leipzig|D																																																											18	Hertha Berlin																													W	L	L	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	|FC Koln|W|Schalke|L																																																											17	Bochum																													L	D	L	D	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	|Freiburg|L|Dortmund|W																																																											8	Mainz																													L	L	W	D	D
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	|Werder Bremen|D|Stuttgart|D																																																											10	B. Monchengladbach																													W	L	L	D	W
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	|RB Leipzig|D|FC Koln|D																																																											14	Hoffenheim																													W	L	L	D	W
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	|Hoffenheim|W|Bochum|L																																																											13	Augsburg																													W	D	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	|Hertha Berlin|D|Eintracht Frankfurt|D																																																											5	Freiburg																													L	W	W	W	L
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	|Bayer Leverkusen|L|Augsburg|W																																																											2	Dortmund																													W	D	W	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	|Schalke|D|B. Monchengladbach|D																																																											6	Bayer Leverkusen																													L	D	W	D	W
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	|Mainz|D|Union Berlin|D																																																											16	Stuttgart																													L	W	D	D	W
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	|Bayern Munich|W|Wolfsburg|L																																																											3	RB Leipzig																													W	W	L	W	W
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	|B. Monchengladbach|W|Hertha Berlin|L																																																											11	FC Koln																													W	L	W	D	W
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	|Wolfsburg|D|Schalke|D																																																											15	Schalke																													W	W	L	W	L
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	|Stuttgart|L|Freiburg|W
26	20.08.2022	Dortmund	1	0	2	3	Werder Bremen	|Dortmund|L|Werder Bremen|W
Sheet3

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LEFT(TRIM(RIGHT(SUBSTITUTE(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/ISNUMBER(SEARCH("|"&$BQ7&"|",$I$5:$I$500)),COLUMNS($CT:CT))),"|"&$BQ7&"|",REPT(" ",100)),100)),1)
I5:I26	I5	=IF(B5="","","|"&C5&"|"&MID("LDW",SIGN(F5-G5)+2,1)&"|"&H5&"|"&MID("WDL",SIGN(F5-G5)+2,1))

 

[VKiprijan]

Do you mean in my results? You listed those blue results as the correct result for Bayern Munich and that is exactly what my results showed:

View attachment 91681

Having said that, I have realised that I used functions in my formulas that you will not have in your 2016 version.

Here is an alternative, still using a helper column (which you can hide) to keep the formulas a bit shorter, that I think should work in your version.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	|Eintracht Frankfurt|L|Bayern Munich|W																																																																																									Last 5 games
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	|Wolfsburg|D|Werder Bremen|D																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	|Union Berlin|W|Hertha Berlin|L																																																											9	Eintracht Frankfurt																													L	D	L	D	L
8	06.08.2022	Bochum	1	1	1	2	Mainz	|Bochum|L|Mainz|W																																																											1	Bayern Munich																													W	W	L	D	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	|B. Monchengladbach|W|Hoffenheim|L																																																											7	Wolfsburg																													L	W	W	D	L
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	|Augsburg|L|Freiburg|W																																																											12	Werder Bremen																													L	L	W	L	D
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	|Dortmund|W|Bayer Leverkusen|L																																																											4	Union Berlin																													L	D	W	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	|Stuttgart|D|RB Leipzig|D																																																											18	Hertha Berlin																													W	L	L	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	|FC Koln|W|Schalke|L																																																											17	Bochum																													L	D	L	D	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	|Freiburg|L|Dortmund|W																																																											8	Mainz																													L	L	W	D	D
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	|Werder Bremen|D|Stuttgart|D																																																											10	B. Monchengladbach																													W	L	L	D	W
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	|RB Leipzig|D|FC Koln|D																																																											14	Hoffenheim																													W	L	L	D	W
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	|Hoffenheim|W|Bochum|L																																																											13	Augsburg																													W	D	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	|Hertha Berlin|D|Eintracht Frankfurt|D																																																											5	Freiburg																													L	W	W	W	L
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	|Bayer Leverkusen|L|Augsburg|W																																																											2	Dortmund																													W	D	W	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	|Schalke|D|B. Monchengladbach|D																																																											6	Bayer Leverkusen																													L	D	W	D	W
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	|Mainz|D|Union Berlin|D																																																											16	Stuttgart																													L	W	D	D	W
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	|Bayern Munich|W|Wolfsburg|L																																																											3	RB Leipzig																													W	W	L	W	W
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	|B. Monchengladbach|W|Hertha Berlin|L																																																											11	FC Koln																													W	L	W	D	W
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	|Wolfsburg|D|Schalke|D																																																											15	Schalke																													W	W	L	W	L
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	|Stuttgart|L|Freiburg|W
26	20.08.2022	Dortmund	1	0	2	3	Werder Bremen	|Dortmund|L|Werder Bremen|W
Sheet3

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LEFT(TRIM(RIGHT(SUBSTITUTE(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/ISNUMBER(SEARCH("|"&$BQ7&"|",$I$5:$I$500)),COLUMNS($CT:CT))),"|"&$BQ7&"|",REPT(" ",100)),100)),1)
I5:I26	I5	=IF(B5="","","|"&C5&"|"&MID("LDW",SIGN(F5-G5)+2,1)&"|"&H5&"|"&MID("WDL",SIGN(F5-G5)+2,1))

No, I actually meant to say that there was a mistake in the results in the formula inserted by Fluff where the second game was indicated as a Loss.

Yes, since I am using the Excel 2016 I will appreciate a lot if you can write me a formula that will work this L5G statistics. It would be great if you can write a formula that will show last 5 games first for home and then for the away games. Thank you so much.

 

[Fluff]

Did the modified version of my formula, or Peter's formula work?

 

[VKiprijan]

No, I actually meant to say that there was a mistake in the results in the formula inserted by Fluff where the second game was indicated as a Loss.

Yes, since I am using the Excel 2016 I will appreciate a lot if you can write me a formula that will work this L5G statistics. It would be great if you can write a formula that will show last 5 games first for home and then for the away games. Thank you so much.

Did the modified version of my formula, or Peter's formula work?

I believe that it did not work because of

what column between G and G have the date of the game played?

the dates are in the B column. Thanks

 

[Fluff]

I believe that it did not work because of

In what way did they not work?