1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlook Again -- Oh No

Discussion in 'Ask an Excel Question' started by Frank Bacchus, Sep 14, 2018.

  1. Frank Bacchus

    Frank Bacchus Member

    Messages:
    153
    Hi all:

    This may be a simple answer:

    I have an excel sheet with a key and a number. The only thing is the key repeats and the number may be different. I would like to do a vlookup and get (as a result) the highest number for that key. For example:

    key - number
    1 - 4
    1 - 5
    1 - 7 My vlookup result should be 7
    2 - 10 My vlookup result should be 10
    3 - 2 My vlookup result should be 2
    4 - 6
    4 - 7
    4 - 9
    4 - 11 My vlookup result should be 11

    Simple - what is the vlookup formula?

    thanks

    frank
  2. azumi

    azumi Active Member

    Messages:
    219
    Try upload your excel file for better picture and describe your expected results
  3. Frank Bacchus

    Frank Bacchus Member

    Messages:
    153
    Thanks. I have uploaded a file. Thanks for your input.

    frank

    Attached Files:

  4. pecoflyer

    pecoflyer Active Member

    Messages:
    256
    Something like =IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))
    Pull down as needed
  5. Frank Bacchus

    Frank Bacchus Member

    Messages:
    153
    Thanks pecoflyer. I could get this to work. Not sure I understand it. Can you please amplify a little or place in sample spreadsheet? Would appreciate it. Thanks.
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,003
    Hi pecoflyer,

    Your formula appear incomplete, maybe missing........

    =IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$9,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))

    Regards
    Bosco
    Last edited: Sep 16, 2018
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,003
    Or............

    In E2, copied down :

    =IF(COUNTIF($A$2:$A2,A2)=ROUNDUP(COUNTIF($A$2:$A$39,A2)/2,0),AGGREGATE(14,6,$B$2:$B$39/($A$2:$A$39=A2),1),"")

    Regards
    Bosco
    Last edited: Sep 16, 2018
  8. pecoflyer

    pecoflyer Active Member

    Messages:
    256
    You're quite right, I deleted the 9 when replacing the semi colon with a comma.
    Thanks
  9. Frank Bacchus

    Frank Bacchus Member

    Messages:
    153
    Bosco & pecoflyer: Thanks a lot. this works like a charm. Appreciate it.

    frank

Share This Page