Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup using =Max() (Excel 2003)

    Hi

    I could use a little help here please, please see attached worksheet

    In column Q I want to lookup the descripton in column A based on the max formula in column R it should read Bactosol Hand Glass Wash 1 Ltr P250589

    Range Name is summary P2:P6

    The formula I tried is =VLOOKUP(MAX(P2:P5),summary,1,0)

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Try

    =INDEX(A2:A5,MATCH(MAX(P2:P5),P2:P5))

    VLOOKUP only works if the search value is in the first column of the array.

  4. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup using =Max() (Excel 2003)

    For VLOOKUP to work the value it is looking up must be in the first column of the table. You can perform a lookup to the left by combining the INDEX and MATCH functions.

    <big><code>=INDEX(summary,MATCH(MAX(P2:P5),P2:P5,0 ),1)</code></big>

  5. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Hi Hans

    Once again thanks very much.

    Just to see if I understand this formula, Index is the total range I need to search for the description - Max is the highest number in the range P2:P5, its the last P2:P5 I cant' quite grasp.

    Thanks for your patience.

    Oh and of course the formula works fine.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Vlookup using =Max() (Excel 2003)

    =<font color=ff8c00>INDEX</font color=ff8c00>(<font color=magenta>A2:A5</font color=magenta>,<font color=448800>MATCH</font color=448800>(<font color=red>MAX(P2:P5)</font color=red>,<font color=blue>P2:P5</font color=blue>))

    <font color=red>MAX(P2:P5)</font color=red> returns the maximum value in <font color=blue>P2:P5</font color=blue>. Let's call this <font color=red>M</font color=red>.

    <font color=448800>MATCH</font color=448800>(<font color=red>MAX(P2:P5)</font color=red>,<font color=blue>P2:P5</font color=blue>) can be shortened to <font color=448800>MATCH</font color=448800>(<font color=red>M</font color=red>,<font color=blue>P2:P5</font color=blue>). It takes the max value <font color=red>M</font color=red> and looks in <font color=blue>P2:P5</font color=blue> to see in which cell it occurs. It returns 1 if the max is in the first cell, 2 if it is in the second cell etc. Let's call this <font color=448800>P</font color=448800>.

    <font color=ff8c00>INDEX</font color=ff8c00>(<font color=magenta>A2:A5</font color=magenta>,<font color=448800>MATCH</font color=448800>(<font color=red>MAX(P2:P5)</font color=red>,<font color=blue>P2:P5</font color=blue>)) can be shortened to <font color=ff8c00>INDEX</font color=ff8c00>(<font color=magenta>A2:A5</font color=magenta>,<font color=448800>P</font color=448800>). It returns the value of the <font color=448800>P</font color=448800>th cell in <font color=magenta>A2:A5</font color=magenta>, i.e. the cell in the same row as the maximum value.

  7. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Hi Hans

    Thank for the explanation the code works fine if put it into the cut down version, However If I adjust the code to suit my main data it just selects the the last row which is row 154

    =INDEX(A2:A154,MATCH(MAX(P2:P154),P2:P154))

    Row P is a formula itself, but I didn't see that a problem as it is a formula in my cutdown sheet where it works.

    Any suggestion where to look please, NB the cut down copy is and exact copy of the first 5 rows of the main data sheet

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Vlookup using =Max() (Excel 2003)

    The following (more resembling Tony's solution) should do what you want:

    =INDEX(A2:A154,MATCH(MAX(P2:P154),P2:P154,0))

    The extra argument 0 in the MATCH function specifies that you are looking for an exact match.

  9. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Hi Hans

    Thanks that did it, and special thanks to Tony.


    Braddy
    If you are a fool at forty, you will always be a fool

  10. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Any chances of a ties? As Index/Match with Max will return only the first instance.

  11. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup using =Max() (Excel 2003)

    Hi there

    There is an outside chance but not very likely.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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