Need value of column A based on the kth largest value in column B with potential dups

mlf

New member
Joined
Nov 21, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
16.31 (mac)
I have alpha-numeric values in column A and numeric values in column B. I need to return the value from column A that corresponds to the kth largest value in column B while correctly dealing with duplicates and without sorting the columns or resorting to VB. E.g.

A
B
Result
1
50035
10.6
nothing
2
xyzzy
3.1
xyzzy
3
12121
10.6
21212
4
21212
5.2
50035
5
nothing
1.9
12121

The order of results based on dups in column B does not mater. It could return 50035, 12121 or 12121, 50035.
I've found several formulas that don't correctly deal with the dups, but still looking for one that does.
 
Maybe this:

=IFERROR(INDEX(A$1:A$6,AGGREGATE(15,6,ROW($2:$6)/($B$2:$B$6=LARGE($B$2:$B$6,1)),ROW(1:1))),"")
 
Back
Top