CODEWITHSUNDEEP
excelexcel-formulaoffice365
guanciale
guanciale

Reputation: 53

Find 2nd highest number in selection then display adjacent cell (includes duplicates) in Excel

So I've got a few rows with data. Row 8 with Names, Row 9 with money amounts and Row 3 with ranking number. You can see via the photo. I'd like D4 to scan Row 9 for the highest money number then display the name above the highest number. D5 would be 2nd highest money amount and so on.

Now I was able to solve this with =INDEX($A$1:$A$6,MATCH(LARGE($B$1:$B$6,D1),$B$1:$B$6,0)) but this doesn't work with duplicates which I have. Any help is appreciated!

enter image description here

Upvotes: 0

Views: 37

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

with a subscription to Office 365 we can use SORT and INDEX.

Put this in D4:

=INDEX(SORT(D8:I9,2,-1,TRUE),1,{1,2,3})

And Excel will spill the results.

enter image description here

Upvotes: 3

Related Questions