Selecting The Max value with VLOOKUP

[DOLANDEE]

Good day all,

I'm having difficulty selecting the Max value using VLOOKUP: eg.

[TABLE="width: 181"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]200[/TD]
[TD="align: center"]50 g[/TD]
[/TR]
[TR]
[TD="align: center"]300[/TD]
[TD="align: center"]100 g[/TD]
[/TR]
[TR]
[TD="align: center"]323[/TD]
[TD="align: center"]150 g[/TD]
[/TR]
[TR]
[TD="align: center"]346[/TD]
[TD="align: center"]200 g[/TD]
[/TR]
[TR]
[TD="align: center"]369[/TD]
[TD="align: center"]300 g[/TD]
[/TR]
[TR]
[TD="align: center"]392[/TD]
[TD="align: center"]300 g[/TD]
[/TR]
[TR]
[TD="align: center"]550[/TD]
[TD="align: center"]350 g[/TD]
[/TR]
[TR]
[TD="align: center"]425[/TD]
[TD="align: center"]400 g[/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]450 g[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]500 g[/TD]
[/TR]
</tbody>[/TABLE]
I want to get the value in column "B", for the max value in column "A".

I used this formula, =VLOOKUP((MAX(A1:A10)),A1:B10,2), but it gives me the max value in column "B" if there is a value less than the max value in column "A" after it, however it works fine if there is a zero value after the max value it.

So basically, I'm getting an answer of "500 g" for this, but the correct answer should be "350 g"

...any thoughts on how is this achieved?

 

[gaz_chops]

Welcome to the forum.

Try

=VLOOKUP((MAX(A1:A10)),A1:B10,2,0)

The zero tells it to find an exact match.

 

[AlanY]

can try this instead

=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))

 

[WaterGypsy]

You need ,FALSE after the 2


edit: I was too slow

 

[DOLANDEE]

Forget it guys, a colleague in my office answered me:

=VLOOKUP((MAX(A1:A10)),A1:B10,2, FALSE) is the correct formula

 

[DOLANDEE]

I was too slow on the draw there too! I didn't expect replies so soon...Thanks a lot guys...very helpful

 

[gaz_chops]

You're welcome, just for info 0 = FALSE.