Forum Discussion
Solved
Duplicate Returns
I've got a football statistics spreadsheet with averages in one column and formations in another. I've tried to return the top 5 averages and the matching formations into other fields in my worksheet.
For the top 5 averages I used:
=LARGE($AX$2:$AX$33, ROWS($O$3:$O3))
To return the formations I used:
=INDEX($AJ$2:$AJ$33,MATCH(LARGE($AX$2:$AX$33,ROWS($M$3:$M3)),$AX$2:$AX$33,0))
It worked great until I noticed two formations had the same average, so it duplicated it. Is there a better command to do what I'm looking for or is there a way to make it pull both formations when the averages match?
Screenshot for reference. (Not concerned with the 0.0 duplicates as they'll populate as data changes.)
=INDEX($B$3:$B$7,MATCH(1,($C$3:$C$7=LARGE($C$3:$C$7,ROW(E1)))*(COUNTIF($G$2:G2,$B$3:$B$7)=0),0))You can try this formula for the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=INDEX($B$3:$B$7,MATCH(1,($C$3:$C$7=LARGE($C$3:$C$7,ROW(E1)))*(COUNTIF($G$2:G2,$B$3:$B$7)=0),0))You can try this formula for the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- curious what your "ROW(E1)" is referencing there. That seems to be what may be throwing it off on my end
ROW(E1) returns 1 in cell H3. When the formula is copied down it returns 2, 3, 4 and so on in cells H4, H5, H6...
In cell G3 and the cells below ROW(E1) is used within the LARGE function. LARGE then returns the 1st largest value in cell G3, the 2nd largest value in cell G4 and so on.
The essential part of ROW(E1) is "1". Instead of ROW(E1) one can apply ROW(A1) or ROW(Z1) or ROW(AF1). The result would always be 1, 2, 3, 4 and 5 in cells H3 to H7.
