Find Max value and display adjacent cell contents

Hi,
I would like a formula to find max value and display adjacent cell contents
example
 Date                        Value
20170230        20
20170417        50
20180323        15
20180930        25

max value  50   display 20170417

Many thanks and wish you all a Happy New Year

Ian
raceproretiredAsked:
Who is Participating?
 
ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
Hi Ian,

Please try below Array Formula, confirmed with Ctrl+Shift+Enter:
=MAX(IF(($A$5:$A$8=$A$2), $B$5:$B$8, ""))

Open in new window

RacePro_MaxValues_v2.xlsx
1
 
ShumsDistinguished Expert - 2017Commented:
Hi Ian,

Assuming your Date Column is in Col A, Values are in Col B and you want result in Col C, try below Formula:
=IF(MAX($B$2:$B$5)=$B2,MAX($B$2:$B$5),"")

Open in new window

Check in attached...
Change the range accordingly
RacePro_MaxValues.xlsx
1
 
raceproretiredAuthor Commented:
Hi Shums,

I was wanting the date actually and place result at top of sheet.
I can find the Max value but would like to know what the date is.
Pls see attached
Thanks
RacePro_MaxValues-V2.xlsx
0
 
raceproretiredAuthor Commented:
I just had to reverse the ranges to find the date.
Works perfectly
Thanks Shums and have a productive and healthy New Year
Ian
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.