I have this excel sheet
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
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.
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:
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.