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 workshee...
=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.
- That works! Thank you!