# Finding nth largest element in 2D range with duplicates

#### chewsters

##### New Member
Hello, I am trying to get some help with a small project.

In short, I would like a sort of "records" page, where I can keep track of what team has scored the most points and when. All records should be distinct; in other words, if the same number of points were scored on different occasions (whether by a different team, different week, same week, etc. etc.)

Currently, this involves me finding the nth largest value within a 2D range, as well as its index (so I can find the correspondent week and team). This works fine when there are no duplicates, as my formula uses a sum-product matching to the nth largest value to locate its row and column. However, it fails for duplicates for obvious reasons.

Fantasy Ball '21.xlsx
ABCDEFGHIJKLMNOPQ
1TeamWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16
2A6346657785378340774747548549
3B259501325264321586416463332328
4C23509538469450621747568600382
5D121465617383568409630477201566
6E99102778291461429353714590406
7F94441606329301247691502151153
8G46551671263540539597492474391
9H92626474316544697689708614391
10I183606523255161338583544392223
11J14845047676410506295468441204
Raw PF

Cell Formulas
RangeFormula
B2:B21B2=LARGE('Raw PF'!\$B\$2:\$Q\$11,A2)
C2:C21C2=SUMPRODUCT(('Raw PF'!\$B\$2:\$Q\$11=B2)*ROW('Raw PF'!\$B\$2:\$Q\$11))-ROW('Raw PF'!\$B\$2:\$Q\$11)+1
D2:D21D2=SUMPRODUCT(('Raw PF'!\$B\$2:\$Q\$11=LARGE('Raw PF'!\$B\$2:\$Q\$11,A2))*COLUMN('Raw PF'!\$B\$2:\$Q\$11))-COLUMN('Raw PF'!\$B\$2:\$Q\$11)+1
E2:E21E2=INDEX('Raw PF'!\$A\$1:\$Q\$11,C2+1,1)
F2:F21F2=INDEX('Raw PF'!\$A\$1:\$Q\$11,1,D2+1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
'Raw PF'!_FilterDatabase='Raw PF'!\$A\$1:\$S\$11E2:F21

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### chewsters

##### New Member
I accidentally posted this before writing everything!
If there are two occasions in which the same number of points are scored, I want both recorded. So for instance, 747 points were scored by Team A on Week 8 and Team C on Week 7. I'd like both listed in whatever order.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Cell Formulas
RangeFormula
B2:B21B2=LARGE('Raw PF'!\$B\$2:\$Q\$11,A2)
C2:C21C2=INDEX('Raw PF'!\$A\$2:\$A\$11,AGGREGATE(15,6,(ROW('Raw PF'!\$A\$2:\$A\$11)-ROW('Raw PF'!\$A\$2)+1)/('Raw PF'!\$B\$2:\$Q\$11=B2),COUNTIFS(B\$2:B2,B2)))
D2:D21D2=INDEX('Raw PF'!\$B\$1:\$Q\$1,AGGREGATE(15,6,(COLUMN('Raw PF'!\$B\$1:\$Q\$1)-COLUMN('Raw PF'!\$B\$1)+1)/('Raw PF'!\$B\$2:\$Q\$11=B2)/('Raw PF'!\$A\$2:\$A\$11=C2),1))

Replies
1
Views
617

1,128,078
Messages
5,628,536
Members
416,323
Latest member
65563

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back