OFFSET and ADDRESS trouble

[Yamezz]

I have a table starting in A6. I have date and times in column A and numerical values in column B.
I want the date and time of the maximum value in column B. To do this I have
=INDIRECT(ADDRESS(MATCH(MAX($B$6:$B$36000),$B$6:$B$36000,0),1))
This gives me a date/time that is 5 rows out from the actual maximum. I presume this is because ADDRESS is returning a row number within the table, rather than the actual row number in Excel.

Q1: Why is it doing this?
Q2: How can I return the correct row number? I've tried to use OFFSET in there, but I can't get it to work.

Any help?
Thanks.

 

[Aladin Akyurek]

Try rather... Control+shift+enter, not just enter:

=IFERROR(SMALL(IF($B$6:$B$36000=MAX($B$6:$B$36000),ROW($B$6:$B$36000)-ROW($B$6)+1),ROWS($1:1)),"")

This gives you one or more relative row numbers at which a MAX instance occurs.

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$6:$A$36000,SMALL(IF($B$6:$B$36000=MAX($B$6:$B$36000),ROW($B$6:$B$36000)-ROW($B$6)+1),ROWS($1:1)),"")

in order to get the corresponding date/time values.

 

[Yamezz]

Thanks Aladin. That's a great help

 

[Aladin Akyurek]

Thanks Aladin. That's a great help

You are welcome.