Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Return date value based on the results of another cell that returns a MAX

    I have dates in cells B5 to B9 and number in C5 to C9. In cell K2, I have a formula to show the maximumvalue between C5 to C9 (=MAX(C5:C9).

    In cell L2, I would like a formula that displayed the date fromcells B5 to B9, based on the formula in K2 that showed the maximum value.
    Example: C7 has the maximumvalue

    B5: 05/15/2011
    B6: 05/16/2011
    B7: 05/17/2011
    B8: 05/18/2011
    B9: 05/19/2011

    C5: 101
    C6: 102
    C7: 150
    C8: 103
    C9: 104

    Results:

    K2 = 150
    L2 = 05/17/2011

    I would appreciate help in writing the formula for cell L2 to give me the date.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Here's one possibility I came up with:
    =OFFSET(FirstDate,(MATCH(K2,MaxLookup,0)-1),0)
    where:
    FirstDate is a range name for B5
    MaxLookup is a range name for C5:C9

    Hope this helps.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    sbdale (2011-05-15)

  4. #3
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    This is exactly what I was looking for. Thanks.

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think this will also work: =index(B5:B9,MATCH(max(C5:C9),C5:C9,0))

  6. The Following User Says Thank You to kweaver For This Useful Post:

    sbdale (2011-05-16)

Posting Permissions

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