How to obtain MAXIF VALUE and it's corresponding cell?

[housy33]

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

<colgroup><col><col><col><col><col></colgroup><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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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!

 

[Special-K99]

=MAX(IF(D2:D10=H1,E2:E10))
Array formula, use Ctrl-Shift-Enter

where H1 is your kg amount you want to find

 

[housy33]

Is there any way to match the name of the lift and the particular date?

 

[Special-K99]

Try

=index(b2:b10,MATCH(MAX(IF(D2:D10=H1,E2:E10)),e2:E10,0),1)
=index(a2:a10,MATCH(MAX(IF(D2:D10=H1,E2:E10)),e2:E10,0),1)

both array formulas, enter as specified before

NOTE: This will work only if the MAXIF VALUE is unique for that lift and date, ie if E4 was 5 instead of 4 like E3 youd only get one value returned