PC Review


Reply
Thread Tools Rate Thread

Vlookup and get the maximum value

 
 
Its me
Guest
Posts: n/a
 
      6th Jun 2009
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
 
Reply With Quote
 
 
 
 
Jacob Skaria
Guest
Posts: n/a
 
      6th Jun 2009
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

 
Reply With Quote
 
 
 
 
Ashish Mathur
Guest
Posts: n/a
 
      6th Jun 2009
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:(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


 
Reply With Quote
 
Teethless mama
Guest
Posts: n/a
 
      6th Jun 2009
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

 
Reply With Quote
 
Jacob Skaria
Guest
Posts: n/a
 
      6th Jun 2009
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA IIf(myCell.Value = Array(myCell2(1).Value, myCell2(2).Value,myCell2(3).Value,myCell2(4).Value) ,"OK","No Match) EagleOne@discussions.microsoft.com Microsoft Excel Programming 2 24th Apr 2009 11:25 PM
Maximum record size and maximum number of records Tom_T Microsoft Access 3 10th Feb 2009 09:09 PM
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED? Edu Microsoft Excel Worksheet Functions 5 3rd Jul 2007 03:16 PM
vlookup for maximum value =?Utf-8?B?R8OhYm9y?= Microsoft Excel Worksheet Functions 4 26th Sep 2006 10:16 PM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:45 PM.