2

I have this excel sheet

enter image description here

I want cell H4 to show the date (column A) corresponding to the maximum value in column H.

In this case it should be 15/1/2016

I would appreciate any help

2 Answers 2

3

You can use the following formula:

=INDEX($A$6:$A$13,MATCH(MAX($H$6:$H$13),$H$6:$H$13,0))

You may need to format the cell as a date to display correctly.

enter image description here

5
  • I used the formula you suggested (with ctrl + shift + enter) but it is not working. It just shows an error message
    – Thomas D.
    Mar 1, 2016 at 10:50
  • I use excel 2003, if it matters
    – Thomas D.
    Mar 1, 2016 at 10:55
  • @ThomasD. What error is it showing?
    – Jonno
    Mar 1, 2016 at 10:57
  • "The formula you typed contains an error"... It's the translation, because i have greek office
    – Thomas D.
    Mar 1, 2016 at 11:05
  • I copy-pasted quite a few times both formulas you suggested and i paid attention to the brackets. Still it's not working :-/
    – Thomas D.
    Mar 1, 2016 at 11:16
0
  1. Use MAX() to identify the maximum value in column H
  2. Use MATCH() to identify which row in Column H contains that value.
  3. Use INDEX() to return the value in column A that is in the row found in step 2.
  4. Use TEXT() to format the result as a date.

If you have a "tie" for maximum value, then the one highest in the list will be used.

=TEXT(INDEX(A6:A13,MATCH(MAX(H6:H13),H6:H13,0),1),"MM/DD/YYYY")

I don't have enough reputation for my screenshot to show automatically, so in this example:

  • A6:A13 contains dates
  • H6:H13 are the values that you're looking through to find the max

And finally, take note of the MATCH() function. It's MATCH(lookup_value,lookup_array,match_type), and I set the match type to zero. This is important if the data that you're matching on is not sorted by ascending values. If the match type is omitted or set to one, then it will do approximate matching which is almost never what you want.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .