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

housy33

New Member
Joined
Jul 9, 2018
Messages
4
A
27/06/2018
B
Deadlift
C
1
D
50
E
8
27/06/2018Deadlift21105
27/06/2018Deadlift31305
27/06/2018Deadlift41304
27/06/2018Deadlift51205

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
28/06/2018Lat Pulldown15010
28/06/2018Lat Pulldown25010
28/06/2018Lat Pulldown3557
28/06/2018Lat Pulldown4509

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

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
=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
Joined
Jul 9, 2018
Messages
4
Is there any way to match the name of the lift and the particular date?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,230
Members
409,857
Latest member
KailuaTown
Top