Formula help needed

[Fraserj]

Hi, I've created a spreadsheet for my football team and need a little help on two of the formulas, that I wish to put into my spreadsheet.

Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Home Name
[/TD]
[TD]Home Goals
[/TD]
[TD]vs
[/TD]
[TD]Away Goals
[/TD]
[TD]Away Name
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]MFC
[/TD]
[TD]7
[/TD]
[TD]-
[/TD]
[TD]2[/TD]
[TD]CFC
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Northern Monkeys
[/TD]
[TD]3
[/TD]
[TD]-
[/TD]
[TD]1
[/TD]
[TD]MFC
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CFC
[/TD]
[TD]4
[/TD]
[TD]-
[/TD]
[TD]2
[/TD]
[TD]MFC
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]MFC
[/TD]
[TD]3
[/TD]
[TD]-
[/TD]
[TD]1
[/TD]
[TD]Northern Monkeys
[/TD]
[/TR]
</tbody>[/TABLE]

These results are on Sheet2 ^^
My Records table is on Sheet1, I would like a Formula to find the biggest home win for MFC but, show as, MFC 7 - 2 CFC

I'd also if possible like a formula with the highest scoring game, (so 7 - 2 is obviously the highest amount of goals in that table) with the result to be shown in the allocated cell as MFC 7 - 2 CFC

Thank you in advance

 

[Comfy]

Something like this:

Excel 2010
	A	B	C	D	E
1	Home Name	Home Goals	vs	Away Goals	Away Name
2	MFC	7	-	2	CFC
3	Northern Monkeys	3	-	1	MFC
4	CFC	4	-	2	MFC
5	MFC	3	-	1	Northern Monkeys
6
7
8
9
10
11
12
13
14
15
16
17
18
19	Largest Home win
20	MFC	7	-	2	CFC
21
22	Highest Scoring Game
23	MFC	7	-	2	CFC
Sheet1

Cell Formulas
Range	Formula
B20	{=INDEX($A$2:$E$5,MATCH(MAX(IF(A2:A5=A20,B2:B5-D2:D5,0)),IF(A2:A5=A20,B2:B5-D2:D5,0),0),2)}
B23	{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),2)}
D20	{=INDEX($A$2:$E$5,MATCH(MAX(IF(C2:C5=A20,D2:D5-F2:F5,0)),IF(C2:C5=A20,D2:D5-F2:F5,0),0),4)}
D23	{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),4)}
E20	{=INDEX($A$2:$E$5,MATCH(MAX(IF(D2:D5=A20,E2:E5-G2:G5,0)),IF(D2:D5=A20,E2:E5-G2:G5,0),0),5)}
E23	{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),5)}
A23	{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fraserj]

Something like this:
Excel 2010

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B20[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX(IF(A2:A5=A20,B2:B5-D2:D5,0)),IF(A2:A5=A20,B2:B5-D2:D5,0),0),2)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D20[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX(IF(C2:C5=A20,D2:D5-F2:F5,0)),IF(C2:C5=A20,D2:D5-F2:F5,0),0),4)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E20[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX(IF(D2:D5=A20,E2:E5-G2:G5,0)),IF(D2:D5=A20,E2:E5-G2:G5,0),0),5)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A23[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),1)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B23[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),2)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D23[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),4)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E23[/TH]
[TD="align: left"]{=INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5+$D$2:$D$5),$B$2:$B$5+$D$2:$D$5,0),5)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank You, Highest Scoring game works perfectly, although, Largest home win isn't working, I don't know whether it's cause cell "A20 doesn't have a formula, or something else?
I've just checked again, it only seems to pick up the 7 - 2, if I change (example) B3 to 10 goals, which would be more they don't change, it only picks up Row 2 for some reason... Any ideas?

 

[Fraserj]

I've sorted it, I just can't get the E20 code to work correctly, the name just says #VALUE!

 

[Comfy]

Yea sorry my references are incorrect.

[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),2)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),4)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),5)}[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fraserj]

What a guy haha, thank you

 

[Fraserj]

Yea sorry my references are incorrect.

[TABLE="width: 100%"]
<tbody>[TR]
[TH="bgcolor: #DAE7F5"]B20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),2)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),4)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E20[/TH]
[TD]{=INDEX($A$2:$E$5,MATCH(MAX(IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0)),IF($A$2:$A$5=A20,$B$2:$B$5-$D$2:$D$5,0),0),5)}[/TD]
[/TR]
</tbody>[/TABLE]

Hi, Sorry about this... I'm trying to add Biggest Away Win, I thought i'd be able to sort it myself from your Formula, but it only finds MFC...I'm thinking I need the opposite of the MATCH function, but I could be completely wrong. Any chance you could help me.

Thank you again in advance

 

[Fraserj]

Nevermind.... I got it when I thought about it... using your formula has given me a better understanding though.. thank you again

 

[Comfy]

You're welcome.

Glad you sorted it.