Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Index/Match problem

    Greetings,

    I have a table in C2:AJG23. In row 2 is the date. In row 23 is a calculated value based on the data in the table. I am looking for help with a combined INDEX and MATCH function that will return the date from row 2 that is in the same column as the maximum calculated value in row 23. Here is what I have written, however, it does not return the correct date:

    =INDEX($C$2:AJG23,1,MAX(INDEX((MAX(C3:AJG23)=C3:AJ G23)*(COLUMN($C$3:AJG23)-COLUMN($B$2)),,)))

    I have stared at this formula and made changes, however, I can't figure out what is wrong with it. I had used a similar formula in another spreadsheet with perfect results so now I am stumped.

    As a second question, if I wanted to use a similar formula to return the date from column 2 when a maximum was found in row 20.

    I appreciate your help!

  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
    JoJoe,

    Welcome to the Lounge as a new poster!

    If I understand your requirements ( a sample file usually helps ) this should help.
    Indexmax.JPG
    Formula: =INDEX($B$1:$I$1,1,MATCH(MAX($B$2:$I$2),$B$2:$I$2, 0))

    Of course I searched on the Date rather than a value and then returned the value associated with the latest date but it will work the other way around just as well. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the welcome! Unfortunately you have me more confused that I was before.

    Here is a snippet of the spreadsheet:

    Capture.JPG

    What I am trying to do is build a formula that would return the value of 3/26/09 because the value on row 22 is at the maximum of 0.028.

    Maybe this will help you help me?

    Thanks

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    What RG was saying is that his formula will work the other way around...as in...

    =INDEX(C2:AJG2,1,MATCH(MAX(C22:AJG22),C22:AJG22,0) )



    However, you didn't say what you wanted if there is a tie for the largest value in row 22.
    Last edited by kweaver; 2013-12-18 at 01:08.

  5. #5
    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
    Kevin,

    Thanks for adjusting the formula to the provided example. :clap:

    JoJoe,

    Maybe this will clear things up.
    Indexmax.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Greetings,

    I cut and pasted Kevin's formula, however, it returned the wrong date. In fact it was a date that was associated with a value in row 22 that was about half of the maximum value found in row 22.

    I can see that this effort is much harder than what I have believed. I see that Kevin's formula is very simple and RetiredGeek's how it should work was informative. Any more ideas?

    Thanks for your help!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you post an example file, demonstrating the issue so we can examine it?

    Steve

Posting Permissions

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