Results 1 to 2 of 2

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

  1. #1
    Neophyte mlf's Avatar
    Join Date
    Nov 2019
    Posts
    1
    Articles
    0
    Excel Version
    16.31 (mac)

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,285
    Articles
    0
    Excel Version
    Office 365 Subscription
    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))),"")
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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