# How to obtain MAXIF VALUE and its corresponding adjacent cell?

Status
Not open for further replies.

#### housy33

##### New Member
[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>

### 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
 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

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

Status
Not open for further replies.

Replies
3
Views
673
Replies
1
Views
57
Replies
6
Views
151
Replies
1
Views
93
Replies
1
Views
32