Copy a row with a max value

[Duncan Feldane]

In an exercise log, I have some data arranged in the following columns: Date, Location, Duration, Distance, Speed.
At the bottom of the data, I'd like to automatically reproduce the row with the max speed, for example.

I found the max speed with: =MAX(E1:E30)
Then I tried to use the following for the date on which the max speed occurred: =ADDRESS(ROW(MAX(E1:E30)),5) and also INDIRECT(that address), but it didn't work.

Please help

 

[Frank Kabel]

Hi Duncan
try the following
=INDEX(A1:A30,MATCH(MAX(E1:E30),E1:E30,0))
HTH
Frank

 

[Ken Wright]

Just adjust the ranges:-

It will match (look up) the Max val in E1:E30, then the MATCH function returns
the row in the range that it appears on, relative to the starting row of the
range (eg, it appears on the 7th row of the range in question), then passes the
7 in this example to the INDEX function, which will run down 7 rows into
whatever range you have specified and return the value in that cell.

=INDEX($D$1:$D$30,MATCH(MAX($E$1:$E$30),$E$1:$E$30,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

In an exercise log, I have some data arranged in the following columns: Date,

Location, Duration, Distance, Speed.

At the bottom of the data, I'd like to automatically reproduce the row with the max speed, for example.

I found the max speed with: =MAX(E1:E30)
Then I tried to use the following for the date on which the max speed

occurred: =ADDRESS(ROW(MAX(E1:E30)),5) and also INDIRECT(that address), but it
didn't work.