Excel - Finding nth largest value with duplicate d

Question

I have a the following table, it has more columns and is 40 rows long but this is an example of the data. The table is sorted by Team #

Data Table

I am trying to create a 2nd table that shows the top 10 teams that delivered gears. I want to do this for the other columns as well. I am trying to do this without VBA.
I used this function and it worked well:

=INDEX(TT_Team,MATCH(LARGE(TT_Tele_Gears,$A3),TT_Tele_Gears,0))

The problem is the duplicate data for the amount of gears delivered IF two teams have delivered the same number of gears I want to show them both, but do not care about which is #1 or #2 Currently I get this:

Top 10 Table

Any ideas on a fix ?

Thanks in Advance

Answer 1

You could try a solution like this:

The formula in F2 copied down is just:

=LARGE(B$2:B$12,D2)

and in E2 as shown it's this:

=INDEX(A$2:A$12,LARGE(IF(B$2:B$12=F2,ROW(B$2:B$12)-ROW(B$2)+1),COUNTIF(F2:F$6,F2)))

confirm with CTRL+SHIFT+ENTER and copy down

It's the COUNTIF part at the end that makes the difference. This is counting from the current row, so for duplicates as you go down the column the COUNTIF value changes, so you get each duplicate