Results 1 to 10 of 10

20050514, 16:55 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20050514, 17:15 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050514, 17:17 #3
 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>

20050514, 17:31 #4
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 Thanks
 2
 Thanked 1 Time in 1 Post
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.
BraddyIf you are a fool at forty, you will always be a fool

20050514, 17:46 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050514, 18:08 #6
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

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

20050514, 18:25 #8
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Vlookup using =Max() (Excel 2003)
Hi Hans
Thanks that did it, and special thanks to Tony.
BraddyIf you are a fool at forty, you will always be a fool

20050514, 18:30 #9
 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.

20050514, 18:37 #10
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Vlookup using =Max() (Excel 2003)
Hi there
There is an outside chance but not very likely.
Thanks
BraddyIf you are a fool at forty, you will always be a fool