Find the Start and End Value in Column in the respective Fields

Hi team, I'm new to the Excel Forum as well as for the excel also.

I have a issue in the excel, that I need to take the result as follows.


Date Value
1/1/2013 10
1/2/2013 10
1/3/2013 10
1/4/2013 10
1/5/2013 10
1/6/2013 12 Search value 10
1/7/2013 12
1/8/2013 12 Result Start End
1/9/2013 12 1/1/2013 1/5/2013
1/10/2013 12
1/11/2013 12
1/12/2013 12
1/13/2013 15
1/14/2013 15
1/15/2013 15
1/16/2013 15
1/17/2013 15
1/18/2013 15
1/19/2013 15
1/20/2013 15
1/21/2013 15
1/22/2013 15
I need to find the start and end date's from the date column from the search value from value column, I have attached my excel sheet also.

hope I explained my question well.
please help me to resolve this.

Originally Posted by :) Sixthsense :)

In J15 Cell

=INDEX(D:D,MATCH(J12,E:E,0))

In K5 Cell

=INDEX(D:D,MATCH(J12,E:E,1))

Great sir.... It's working... Thank you

Originally Posted by benishiryo

welcome to the forum, Ganesankadarkarai. if your dates are arranged in ascending order, try this in J15:
=INDEX(D7:D28,MATCH(J12,E7:E28,0))
and in K15:
=LOOKUP(2,1/(E7:E28=J12),D7:D28)

if they're not, and you want to find the earliest/latest date with the search value, then try this array formula:
=MIN(IF(E7:E28=J12,D7:D28))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

change to MAX for K15. or use this SUMPRODUCT formula:
=SUMPRODUCT(MAX((E7:E28=J12)*D7:D28))

Great sir.... It's working... Thank you so much for both of you.