# Vlookup and get the maximum value

Discussion in 'Microsoft Excel Worksheet Functions' started by Its me, Jun 6, 2009.

1. ### Its meGuest

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

Its me, Jun 6, 2009

2. ### Jacob SkariaGuest

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

Jacob Skaria, Jun 6, 2009

3. ### Ashish MathurGuest

Hi,

Try this

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

C9 holds 10

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Its me" <> wrote in message
news:...
> 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

Ashish Mathur, Jun 6, 2009
4. ### Teethless mamaGuest

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

Teethless mama, Jun 6, 2009
5. ### Jacob SkariaGuest

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

Jacob Skaria, Jun 6, 2009