Vlookup and get the maximum value
 

I have a col. A and Col. B


Column A Column B

10 44363
10 56634
20 26348
20 32688


If i enter "10" in column C, it should lookup in Col. A and return the max.
value of Col. B (56634)


Thru vlookup i am getting the only the first value only.

Could someone help in this regard

Vlookup and get the maximum value
 

You need to use MAX + IF condition. With your data in ColA:B

In C1 enter 10
In D1 enter the below formula

=MAX(IF(A:A=C1,B:B))

If this post helps click Yes
---------------
Jacob Skaria


"Its me" wrote:

I have a col. A and Col. B


Column A Column B

10 44363
10 56634
20 26348
20 32688


If i enter "10" in column C, it should lookup in Col. A and return the max.
value of Col. B (56634)


Thru vlookup i am getting the only the first value only.

Could someone help in this regard

Vlookup and get the maximum value
 

Hi,

Try this

=MAX(INDEX((C4:C7=C9)*(D4:D7),,1))

C9 holds 10

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Its me" wrote in message
...
I have a col. A and Col. B


Column A Column B

10 44363
10 56634
20 26348
20 32688


If i enter "10" in column C, it should lookup in Col. A and return the
max.
value of Col. B (56634)


Thru vlookup i am getting the only the first value only.

Could someone help in this regard

Vlookup and get the maximum value
 

Your formula will not work in older version.


"Jacob Skaria" wrote:

You need to use MAX + IF condition. With your data in ColA:B

In C1 enter 10
In D1 enter the below formula

=MAX(IF(A:A=C1,B:B))

If this post helps click Yes
---------------
Jacob Skaria


"Its me" wrote:

I have a col. A and Col. B


Column A Column B

10 44363
10 56634
20 26348
20 32688


If i enter "10" in column C, it should lookup in Col. A and return the max.
value of Col. B (56634)


Thru vlookup i am getting the only the first value only.

Could someone help in this regard

Vlookup and get the maximum value
 

Thanks for pointing that out...For 2003 use the below

=MAX(IF(A1:A100=C1,B1:B100))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Teethless mama" wrote:

Your formula will not work in older version.


"Jacob Skaria" wrote:

You need to use MAX + IF condition. With your data in ColA:B

In C1 enter 10
In D1 enter the below formula

=MAX(IF(A:A=C1,B:B))

If this post helps click Yes
---------------
Jacob Skaria


"Its me" wrote:

I have a col. A and Col. B


Column A Column B

10 44363
10 56634
20 26348
20 32688


If i enter "10" in column C, it should lookup in Col. A and return the max.
value of Col. B (56634)


Thru vlookup i am getting the only the first value only.

Could someone help in this regard