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

2. 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

3. 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.

Thanks

4. 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.

5. Kevin,

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

JoJoe,

Maybe this will clear things up.
Indexmax.JPG
HTH

6. 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?