asked on 4/9/2017

Find Date of highest and Second highest Values & lowest values in a Range Excel formula or VBA

I am looking for a formula to find the Date/Time in Col A for the highest & second highest Value in Col C. The same for the lowest value and second lowest value in Col D.

In sheet 1 is the data

1. Search the H1/H2 vice versa L1/L2 values in a range and extract the date/time
2. Populate the extracted date/time in sheet 2 (calc)

Any assistance will be greatly appreciated.

Thank you.

Regards,

Adam
Sample.xlsx

VBA Microsoft Excel Microsoft Office

Last Comment

Adam Elsheimer

4/9/2017

Rgonzo1971

4/9/2017

Hi,

pls try

=INDEX(Data!A:A,MATCH(MAX(Data!D:D),Data!D:D,0))
=INDEX(Data!A:A,MATCH(LARGE(Data!D:D,2),Data!D:D,0))
=INDEX(Data!A:A,MATCH(MIN(Data!C:C),Data!C:C,0))
=INDEX(Data!A:A,MATCH(SMALL(Data!C:C,2),Data!C:C,0))

Open in new window

Regards
Sample--12-.xlsx

Adam Elsheimer

4/9/2017

ASKER

Thanks.

I am so sorry, but I forget to mention that the second highest/second lowest value should not be the same date as the highest/lowest value day.
I don't know how to exclude the date of the highest/lowest values after finding the values and search for the second values in the remaining dates.

I am so sorry to forget this important condition.

Regards,

ASKER CERTIFIED SOLUTION

Rgonzo1971

4/9/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Adam Elsheimer

4/9/2017

ASKER

Rgonzo many thanks for your very fast and uncomplicated help. It works fine.
I adjusted the columns. Col C and D was incorrectly assigned.

Find Date of highest and Second highest Values &amp; lowest values in a Range Excel formula or VBA

Find Date of highest and Second highest Values & lowest values in a Range Excel formula or VBA