LARGE and INDEX/MATCH help please

[clatron]

I have a table like the below, trying to find top 5 attendances using LARGE then INDEX/MATCH

The issue I have is that in GameWeek1, two teams have the same attendance therefore the INDEX/MATCH brings the first one back twice.
For example, in G1:G5 I have the numbers 1 to 5.
In H1:H5 I have this formula ... =LARGE(IF(($B$2:$B$17="PL")*($C$2:$C$17=1),$D$2:$D$17),$G1)
In I1:I5 I have this formula ... =INDEX($A$2:$A$17,MATCH(1,(1=$C$2:$C$17)*("PL"=$B$2:$B$17)*($H1=$D$2:$D$17),0))

Results 4 and 5 both show as 48000 - which is correct - but the index /match brings back Man City twice when it should be Man City / Spurs.

Any help please? Thanks



[TABLE="width: 329"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Club[/TD]
[TD]League[/TD]
[TD]GameWeek[/TD]
[TD]Attendence[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD]PL[/TD]
[TD]1[/TD]
[TD]52000[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD]PL[/TD]
[TD]1[/TD]
[TD]55000[/TD]
[/TR]
[TR]
[TD]Leeds[/TD]
[TD]Champ[/TD]
[TD]1[/TD]
[TD]31000[/TD]
[/TR]
[TR]
[TD]Man City[/TD]
[TD]PL[/TD]
[TD]1[/TD]
[TD]48000[/TD]
[/TR]
[TR]
[TD]Man Utd[/TD]
[TD]PL[/TD]
[TD]1[/TD]
[TD]76000[/TD]
[/TR]
[TR]
[TD]Spurs[/TD]
[TD]PL[/TD]
[TD]1[/TD]
[TD]48000[/TD]
[/TR]
[TR]
[TD]Villa[/TD]
[TD]Champ[/TD]
[TD]1[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]Wolves[/TD]
[TD]Champ[/TD]
[TD]1[/TD]
[TD]26000[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[TD]54000[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[TD]53000[/TD]
[/TR]
[TR]
[TD]Leeds[/TD]
[TD]Champ[/TD]
[TD]2[/TD]
[TD]29000[/TD]
[/TR]
[TR]
[TD]Man City[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[TD]45000[/TD]
[/TR]
[TR]
[TD]Man Utd[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[TD]72000[/TD]
[/TR]
[TR]
[TD]Spurs[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[TD]46000[/TD]
[/TR]
[TR]
[TD]Villa[/TD]
[TD]Champ[/TD]
[TD]2[/TD]
[TD]28000[/TD]
[/TR]
[TR]
[TD]Wolves[/TD]
[TD]Champ[/TD]
[TD]2[/TD]
[TD]28000[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

You could use this if you really need a formula. In I1:

=INDEX($A$2:$A$17,SMALL(IF($D$2:$D$17=H1,ROW($A$2:$A$17)-ROW($A$2)+1),COUNTIF($H$1:H1,H1)))

Enter CTRL_SHIFT_ENTER. Id use a pivot for this kind of job though. Your data is perfect for it.

 

[Scott T]

You could also use a pivot table.

 

[steve the fish]

Yeah thats what i said