Why does this cause a SPILL error?

[tigerzen]

Simple spreadsheet that should return the game that recorded the particular winning margin. The repeat value (50) causes a SPILL error and I can't work out why.

Book1
	B	C	D	E	F	G	H	I	J	K
2	Team 1	Team 2	Margin	Year		Biggest margins	Game
3	Dragons	Crocs	34	2012		50	#SPILL!
4	Dragons	Eagles	50	2013		50	#SPILL!
5	Dragons	Falcons	18	2014		34	Dragons	Crocs	34	2012
6	Dragons	Crows	50	2015
7	Dragons	Bullets	12	2016
Sheet10

Cell Formulas
Range	Formula
G3:G5	G3	=LARGE($D$3:$D$7,ROW(A1))
H3:H4,H5:K5	H3	=FILTER($B$3:$E$7,$D$3:$D$7=G3)

 

[awoohaw]

Your FILTER FUNCTION is returning two rows in both H3 and H4. This is because you have two values of 50 (your largest margin). You need to have some kind of tiebreaker system.

 

[tigerzen]

Your FILTER FUNCTION is returning two rows in both H3 and H4. This is because you have two values of 50 (your largest margin). You need to have some kind of tiebreaker system.

Thanks Awoohaw, what would be the simplest way?

 

[AhoyNC]

Here is an option using INDEX.

Book1
	B	C	D	E	F	G	H	I	J	K
2	Team 1	Team 2	Margin	Year		Biggest margins	Game
3	Dragons	Crocs	34	2012		50	Dragons	Eagles	50	2013
4	Dragons	Eagles	50	2013		50	Dragons	Crows	50	2015
5	Dragons	Falcons	18	2014		34	Dragons	Crocs	34	2012
6	Dragons	Crows	50	2015
7	Dragons	Bullets	12	2016
Sheet1

Cell Formulas
Range	Formula
G3:G5	G3	=LARGE($D$3:$D$7,ROW(A1))
H3:K5	H3	=INDEX($B$3:$E$7,AGGREGATE(15,6,(ROW($D$3:$D$7)-ROW($D$3)+1)/($D$3:$D$7=$G3),COUNTIF($G$3:G3,G3)),0)
Dynamic array formulas.

 

[tigerzen]

Here is an option using INDEX.

Book1
	B	C	D	E	F	G	H	I	J	K
2	Team 1	Team 2	Margin	Year		Biggest margins	Game
3	Dragons	Crocs	34	2012		50	Dragons	Eagles	50	2013
4	Dragons	Eagles	50	2013		50	Dragons	Crows	50	2015
5	Dragons	Falcons	18	2014		34	Dragons	Crocs	34	2012
6	Dragons	Crows	50	2015
7	Dragons	Bullets	12	2016
Sheet1

Cell Formulas
Range	Formula
G3:G5	G3	=LARGE($D$3:$D$7,ROW(A1))
H3:K5	H3	=INDEX($B$3:$E$7,AGGREGATE(15,6,(ROW($D$3:$D$7)-ROW($D$3)+1)/($D$3:$D$7=$G3),COUNTIF($G$3:G3,G3)),0)
Dynamic array formulas.

Thanks Ahoy.

 

[RoryA]

You could replace all your formulas with something like:

=SORT(FILTER($B$3:$E$7,$D$3:$D$7>=LARGE(D3:D7,3)),3,-1)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Team 1	Team 2	Margin	Year		Biggest margins	Game
3		Dragons	Crocs	34	2012		50	Dragons	Eagles
4		Dragons	Eagles	50	2013		50	Dragons	Crows
5		Dragons	Falcons	18	2014		34	Dragons	Crocs
6		Dragons	Crows	50	2015
7		Dragons	Bullets	12	2016
Master

Cell Formulas
Range	Formula
G3:I5	G3	=TAKE(SORT(CHOOSECOLS(B3:D7,3,1,2),,-1),3)
Dynamic array formulas.