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

[housy33]

[TABLE="width: 604"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A
27/06/2018[/TD]
[TD]B
Deadlift[/TD]
[TD]C
1[/TD]
[TD]D
50[/TD]
[TD="align: right"]E
8[/TD]
[/TR]
[TR]
[TD]27/06/2018[/TD]
[TD]Deadlift[/TD]
[TD]2[/TD]
[TD]110[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]27/06/2018[/TD]
[TD]Deadlift[/TD]
[TD]3[/TD]
[TD]130[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]27/06/2018[/TD]
[TD]Deadlift[/TD]
[TD]4[/TD]
[TD]130[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]27/06/2018[/TD]
[TD]Deadlift[/TD]
[TD]5[/TD]
[TD]120[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 604"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]28/06/2018[/TD]
[TD]Lat Pulldown[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]28/06/2018[/TD]
[TD]Lat Pulldown[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]28/06/2018[/TD]
[TD]Lat Pulldown[/TD]
[TD]3[/TD]
[TD]55[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]28/06/2018[/TD]
[TD]Lat Pulldown[/TD]
[TD]4[/TD]
[TD]50[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

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