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

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
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,114,189
Messages
5,546,470
Members
410,742
Latest member
WalterSil
Top