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

#### housy33

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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Special-K99

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

where H1 is your kg amount you want to find

#### housy33

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

#### Special-K99

##### Well-known Member
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

Replies
3
Views
947
Replies
1
Views
60
Replies
6
Views
153
Replies
1
Views
75
Replies
1
Views
94