Formula for next upcoming fixture

[speedychaz94]

Hi there,

It's been a while since I have posted here. I've had great help in the past so fingers crossed the same can be said for this time.

Anyway, I have a fantasy premier league league which I am the league admin of. This season we are doing a head-to-head format which is posing new challenges. I have created an 'H2H fixtures' sheet with all fixtures 38 Gameweek (GW) matches for all 84 players. Only 3 GWs have been played. I would like is to have the next fixture for each player in P4:P87 in the H2H Standings sheet. Can someone help? This is beyond my Excel skills.

Bonus points if able to colour-code the upcoming fixture with fixture difficulty, based on player rank.

I hope this makes sense. Let me know if not.

Thanks in advance.

Charlie

Fantasy 2425 H2H sample.xlsx

 

[myall_blues]

Is this what you mean by 'next fixture'?

Fantasy 2425 H2H sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3	Pos	Manager	P	W	D	L	F	A	Pts	GD	GOT Rnk	Win Strk	U/B Strk	Losing Strk	W/out Win Strk	GW4
4	1	Ryan Toles	3	3	0	0	243	178	9	65	2	3	3	0	0	John McOmish
5	2	Rory Anderson	3	3	0	0	233	208	9	25	9	3	3	0	0	Mark Black
6	3	Hafsa Rafiq	3	3	0	0	228	168	9	60	15	3	3	0	0	Arek Pienkowski
7	4	Mike Brown	3	3	0	0	227	190	9	37	17	3	3	0	0	André Mårtensson
8	5	Chris Tennant	3	3	0	0	226	141	9	85	18	3	3	0	0	Sheepie Smith
9	6	Ryan Pendreich	3	3	0	0	226	174	9	52	19	3	3	0	0	Peter Kettle
10	7	Riley Simpson	3	3	0	0	223	191	9	32	24	3	3	0	0	Ryan Lennie
11	8	Chris Boyd	3	3	0	0	221	189	9	32	27	3	3	0	0	Carl Roderick
12	9	Greg Louden	3	3	0	0	213	159	9	54	39	3	3	0	0	Diego Dominguez
13	10	Joshua Purves	3	3	0	0	196	152	9	44	56	3	3	0	0	Gerard McKenna
H2H Standings

Cell Formulas
Range	Formula
P3	P3	="GW"&C4+1
P4:P13	P4	=LET(ffilt,FILTER('H2H Fixtures'!$B$3:$EW$44,'H2H Fixtures'!$B$2:$EW$2=C4+1), XLOOKUP(B4,FILTER(ffilt,{1,0,0,0}),FILTER(ffilt,{0,0,0,1}),XLOOKUP(B4,FILTER(ffilt,{0,0,0,1}),FILTER(ffilt,{1,0,0,0}))))

 

[speedychaz94]

Is this what you mean by 'next fixture'?

Fantasy 2425 H2H sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3	Pos	Manager	P	W	D	L	F	A	Pts	GD	GOT Rnk	Win Strk	U/B Strk	Losing Strk	W/out Win Strk	GW4
4	1	Ryan Toles	3	3	0	0	243	178	9	65	2	3	3	0	0	John McOmish
5	2	Rory Anderson	3	3	0	0	233	208	9	25	9	3	3	0	0	Mark Black
6	3	Hafsa Rafiq	3	3	0	0	228	168	9	60	15	3	3	0	0	Arek Pienkowski
7	4	Mike Brown	3	3	0	0	227	190	9	37	17	3	3	0	0	André Mårtensson
8	5	Chris Tennant	3	3	0	0	226	141	9	85	18	3	3	0	0	Sheepie Smith
9	6	Ryan Pendreich	3	3	0	0	226	174	9	52	19	3	3	0	0	Peter Kettle
10	7	Riley Simpson	3	3	0	0	223	191	9	32	24	3	3	0	0	Ryan Lennie
11	8	Chris Boyd	3	3	0	0	221	189	9	32	27	3	3	0	0	Carl Roderick
12	9	Greg Louden	3	3	0	0	213	159	9	54	39	3	3	0	0	Diego Dominguez
13	10	Joshua Purves	3	3	0	0	196	152	9	44	56	3	3	0	0	Gerard McKenna
H2H Standings

Cell Formulas
Range	Formula
P3	P3	="GW"&C4+1
P4:P13	P4	=LET(ffilt,FILTER('H2H Fixtures'!$B$3:$EW$44,'H2H Fixtures'!$B$2:$EW$2=C4+1), XLOOKUP(B4,FILTER(ffilt,{1,0,0,0}),FILTER(ffilt,{0,0,0,1}),XLOOKUP(B4,FILTER(ffilt,{0,0,0,1}),FILTER(ffilt,{1,0,0,0}))))

This is perfect. Many thanks

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula for next upcoming fixture
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[speedychaz94]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula for next upcoming fixture
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I am sorry about this. I wasn't aware. Would you like me to provide a link? I only posted on one other forum.

 

[Fluff]

Would you like me to provide a link? I only posted on one other forum.

No that's ok as I have already posted the link.