# find first and latest dates that equal the value today

#### Cheeks1969

##### New Member
Let's say I get a datapoint of something that fluctuates over time and I enter it with a date down two columns.

I want to find the last date where it was equal to, or less than the current value. And I want to find the first time it was equal to or higher than that value.

An example might be the stock market. If I had the weekly closing Dow Avg as the data point and the date of every friday in the date column.
Say the market goes up and down and now it's crashing. I enter the closing price today and want to use that to find when it was last at this level, and when it was first at this level.

I found a solution that finds the last match, but nothing that would find the date it was last < or =. For the "first time it hit this level" it would have to be the first datapoint that is >= than current.

Thanks

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
C3: =IFERROR(INDEX(A\$2:A2,MATCH(TRUE,B\$2:B2>=B3,)),"-") array-entered
D3: =IFERROR(LOOKUP(2,1/(B\$2:B2<=B3),A\$2:A2),"-")

Book1
ABCD
1DatePriceFirst >=Last <=
24 Jan 2019980
311 Jan 2019988-4 Jan 2019
418 Jan 20191,001-11 Jan 2019
525 Jan 20191,005-18 Jan 2019
61 Feb 201999018 Jan 201911 Jan 2019
78 Feb 201999818 Jan 20191 Feb 2019
815 Feb 20191,00425 Jan 20198 Feb 2019
922 Feb 20191,007-15 Feb 2019
101 Mar 20191,009-22 Feb 2019
118 Mar 20191,016-1 Mar 2019
1215 Mar 20191,017-8 Mar 2019
1322 Mar 20191,00722 Feb 201922 Feb 2019
1429 Mar 20191,00018 Jan 20198 Feb 2019
155 Apr 201999918 Jan 20198 Feb 2019
1612 Apr 201999718 Jan 20191 Feb 2019
1719 Apr 201998918 Jan 201911 Jan 2019
1826 Apr 20199804 Jan 20194 Jan 2019
193 May 20199754 Jan 2019-
Sheet1

Last edited:
Perfect! Thank you

Replies
1
Views
99
Replies
1
Views
166
Replies
1
Views
294
Replies
1
Views
430
Replies
2
Views
321

1,206,718
Messages
6,074,500
Members
446,072
Latest member
OrangeYellow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back