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

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

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.

2. 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))),"")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•