Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Column 1 contains a list of names, and column 2 calculates the number of lunches that person attended. I know that I can use the MAX function on column 2 to determine the highest number in the lunches column, but how do I determine the name of the person who matches the MAX number?

    I am using Excel 2000/SP-3

    Thanks for your help.
    Attached Images Attached Images
    Thanks,
    Caroline in lala-land

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    The attached example should solve your dilemma. Note that there may be more than one person with the most. Also note that in rows 2:6, columns E:G have the same formulae; Row 1 is unique.

    [attachment=83268:Most_Lunches.xls]
    Attached Files Attached Files
    Regards
    Don

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='769772' date='08-Apr-2009 11:22']The attached example should solve your dilemma. Note that there may be more than one person with the most. Also note that in rows 2:6, columns E:G have the same formulae; Row 1 is unique.[/quote]

    Thank you for your help! I'll give a try.
    Thanks,
    Caroline in lala-land

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    An easier formula is to use:

    =LOOKUP(MAX(B2:B100),B2:B100,A2:A100)

    Assuming the list of diners is in range A2:A100. Adjust to fit
    Jerry

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Jezza' post='769817' date='08-Apr-2009 18:36']An easier formula is to use:

    =LOOKUP(MAX(B2:B100),B2:B100,A2:A100)

    Assuming the list of diners is in range A2:A100. Adjust to fit[/quote]

    I don't think that approach will work in this instance. Note the following from LOOKUP Help

    Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •