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

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

Hi,

Try this

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

C9 holds 10

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Its me" <(E-Mail Removed)> wrote in message
news:EFCD3770-9D85-464B-9B5A-(E-Mail Removed)...
> 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

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

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