Vlookup and get the maximum value

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

  1. Its me

    Its me Guest

    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
    #1
    1. Advertisements

  2. Its me

    Jacob Skaria Guest

    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
    #2
    1. Advertisements

  3. 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
    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
    #3
  4. 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
    #4
  5. Its me

    Jacob Skaria Guest

    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
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

    Guest, Jan 26, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    1,016
  2. Guest

    vlookup for maximum value

    Guest, Sep 26, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    735
    Guest
    Sep 26, 2006
  3. Edu

    EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?

    Edu, Jul 3, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    1,146
    Guest
    Jul 3, 2007
  4. AndyCotgreave

    Looking up a maximum value that matches criteria. VLOOKUP?

    AndyCotgreave, Oct 2, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    234
  5. dpk

    maximum value received through vlookup

    dpk, Mar 5, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    330
    Pete_UK
    Mar 5, 2008
Loading...

Share This Page