A formula to select the highest cell in a column that then shows the entire row

[AlphaRino]

I would like to do the following:

Find the highest cell in a column in an excel spreadsheet. Display that entire row.

I have a spreadsheet that tracks our Walks that shows



Number of Walks (Column B)

Date (Column C)

Location (Column D)

Distance in Miles (Column E)

Total Elevation in Feet (Column F)

Total Time HH:MM (Column G)

Moving Time HH:MM (Column H)

Stopped Time HH:MM (Column I)

Average Speed MPH (Column J)

Average Moving Speed MPH (Column K)



I would like to use formulas to display the entire row of the longest walk, the highest elevation and fastest walk etc. On a different Sheet

 

[etaf]

you should be able to use FILTER() to show that info
so assuming B2:K1000 - change range to suite

=FILTER(B2:K13,E2:E13=MAX(E2:E13))
will pull out the row with the highest mileage
you can use a + to include the other rows
=FILTER(B2:K13,(E2:E13=MAX(E2:E13))+(F2:F13=MAX(F2:F13)))
to pull out the shortest walk - then MIN total time
=FILTER(B2:K13,((E2:E13=MAX(E2:E13))+(F2:F13=MAX(F2:F13))+(G2:G13=MIN(G2:G13))*(B2:B13<>"")))
and to avoid blank cells where no data entered into WALK

for simplicity I have added to one sheet for display here

BUT to add to a different sheet - just add the sheetname to the range

Book5
	A	B	C	D	E	F	G	H	I	J	K
1		Number of Walks (Column B)	Date (Column C)	Location (Column D)	Distance in Miles (Column E)	Total Elevation in Feet (Column F)	Total Time HH:MM (Column G)	Moving Time HH:MM (Column H)	Stopped Time HH:MM (Column I)	Average Speed MPH (Column J)	Average Moving Speed MPH (Column K)
2		1	1/1/23	A1	1.2	1	1:32
3		2	1/2/23	A2	4.6	2	1:45
4		3	1/3/23	A3	12.1	3	2:45
5		4	1/4/23	A4	2.1	4	1:32
6		5	1/5/23	A5	1.1	5	1:45
7		6	1/6/23	A6	3.41	1	1:02
8		7	1/7/23	A7	3.14	2	1:32
9		8	1/8/23	A8	2.87	3	1:45
10		9	1/9/23	A9	2.6	4	1:02
11		10	1/10/23	A10	2.33	5	1:32
12		11	1/11/23	A11	2.06	6	1:45
13
14
15
16
17
18
19		3	44929	A3	12.1	3	02:45:00	0	0	0	0
20		6	44932	A6	3.41	1	01:02:00	0	0	0	0
21		9	44935	A9	2.6	4	01:02:00	0	0	0	0
22		11	44937	A11	2.06	6	01:45:00	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B19:K22	B19	=FILTER(B2:K13,((E2:E13=MAX(E2:E13))+(F2:F13=MAX(F2:F13))+(G2:G13=MIN(G2:G13))*(B2:B13<>"")))
Dynamic array formulas.

 

[AlphaRino]

Perfect Thanks

 

[etaf]

you are welcome

 

[Joe4]

Perfect Thanks

Please note:
In the future, when marking a post as the solution, please mark the actual post containing the solution, not your own post acknowledging some other post is the solution.
I have gone ahead and updated this thread for you.