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![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
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
Club | League | GameWeek | Attendence |
Arsenal | PL | 1 | 52000 |
Chelsea | PL | 1 | 55000 |
Leeds | Champ | 1 | 31000 |
Man City | PL | 1 | 48000 |
Man Utd | PL | 1 | 76000 |
Spurs | PL | 1 | 48000 |
Villa | Champ | 1 | 30000 |
Wolves | Champ | 1 | 26000 |
Arsenal | PL | 2 | 54000 |
Chelsea | PL | 2 | 53000 |
Leeds | Champ | 2 | 29000 |
Man City | PL | 2 | 45000 |
Man Utd | PL | 2 | 72000 |
Spurs | PL | 2 | 46000 |
Villa | Champ | 2 | 28000 |
Wolves | Champ | 2 | 28000 |
<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>