How to obtain MAXIF VALUE and its corresponding adjacent cell?

Status
Not open for further replies.

housy33

New Member
Joined
Jul 9, 2018
Messages
4
[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/2018Deadlift21105
27/06/2018Deadlift31305
27/06/2018Deadlift41304
27/06/2018Deadlift51205

<tbody>
</tbody>

28/06/2018Lat Pulldown15010
28/06/2018Lat Pulldown25010
28/06/2018Lat Pulldown3557
28/06/2018Lat Pulldown4509

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
27/06/2018Deadlift1508
27/06/2018Deadlift21105
27/06/2018Deadlift31305
27/06/2018Deadlift41304
27/06/2018Deadlift51205
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

New Member
Joined
Jul 9, 2018
Messages
4
This formula only returns the number 5, is there any way that this formula can be dragged down ?
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,113,939
Messages
5,545,110
Members
410,658
Latest member
kris_friis
Top