Forum Discussion

Copper Contributor

Jan 23, 2023

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...

excel

Formulas and Functions

OliverScheurich
Jan 23, 2023
rushy24
=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.

OliverScheurich

Gold Contributor

Jan 23, 2023

rushy24

=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.

rushy24

Copper Contributor

Jan 24, 2023

curious what your "ROW(E1)" is referencing there. That seems to be what may be throwing it off on my end

OliverScheurich
Gold Contributor
Jan 24, 2023
rushy24
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.
- rushy24
  Copper Contributor
  Jan 24, 2023
  That works! Thank you!