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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

  3. #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>

  4. #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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

  6. #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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

  8. #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

  9. #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.

  10. #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
  •