I have a record of dates and values.
Column A holds dates, column B holds values.
In my formula I am calculating the difference between a cell in the past and the latest cell:
<code>=B25-LOOKUP(2,1/(B:B<>""),B:B)
</code>
<code>=B25-LOOKUP(2,1/(B:B<>""),B:B)
</code>
=B25-LOOKUP(2,1/(B:B<>""),B:B)
where the B column contains the values.
B25 is for a date 10 weeks ago.
I increment B25 each week. So next week it will be B26.
In this way my difference calculation is for the previous 10 weeks.
Is there a formula that will allow me to select the B cell based on the date in the A column being 10 weeks earlier than today?