Vlookup using =Max() (Excel 2003)

[braddy60]

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

[HansV]

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.

[TonyE]

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>

[braddy60]

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

[HansV]

=<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.

[braddy60]

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

[HansV]

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.

[braddy60]

Hi Hans

Thanks that did it, and special thanks to Tony.


Braddy

[maxflia10]

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

[braddy60]

Hi there

There is an outside chance but not very likely.

Thanks

Braddy