How to obtain MAXIF VALUE and its corresponding adjacent cell?

[housy33]

[h=1]Hi guys,

I am struggling to come up with a formula that will be able to obtain the MAX VALUE of Column D and the its corresponding cell to the right. e.g. for my deadlift, i want to use 130kg and return the number 5 (reps) in a cell, or for lat pulldown I want 55kg and 7. Any help would be much appreciated.

Thanks![/h]

A 27/06/2018	B Deadlift	C 1	D 50	E 8
27/06/2018	Deadlift	2	110	5
27/06/2018	Deadlift	3	130	5
27/06/2018	Deadlift	4	130	4
27/06/2018	Deadlift	5	120	5

<tbody>
</tbody>

28/06/2018	Lat Pulldown	1	50	10
28/06/2018	Lat Pulldown	2	50	10
28/06/2018	Lat Pulldown	3	55	7
28/06/2018	Lat Pulldown	4	50	9

<tbody>
</tbody>

 

[oldbrewer]

27/06/2018	Deadlift	1	50	8
27/06/2018	Deadlift	2	110	5
27/06/2018	Deadlift	3	130	5
27/06/2018	Deadlift	4	130	4
27/06/2018	Deadlift	5	120	5
					5
					formula
					=OFFSET($D$1,MATCH(MAX($D$2:$D$6),$D$2:$D$6,0),1)

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

 

[housy33]

This formula only returns the number 5, is there any way that this formula can be dragged down ?

 

[RoryA]

Already posted here: https://www.mrexcel.com/forum/excel...btain-maxif-value-its-corresponding-cell.html

Please do not post the same question multiple times. Thread closed.