most recent greater and lower date

[dweb]

I have two columns of data, the first are dates in ascending fashion (column B) and the second are corresponding readings (column D). I need a formula that looks back in history and returns the date for the last reading that was greater than a specified date (not just the most current date) as well as the last reading with a lower value. Using Apr 2006 as the current date the results should be Feb 2006 and Mar 2006 respectively. Dates are entered using a dropdown in cell C175.

I should mention that I have already created a named range function that creates a dynamic range based on the time period in focus. So if the time period in focus is Oct 2005 the range automatically references only dates older/above Oct 2005. I included a min and max output to illustrate how the outputs are driven by the date dropdown.

The value in cell C177 is a reference cell used to handle the dynamic range function

I searched through the forums and couldn't find any threads that appeared to address my question. Apologies in advance as I imagine this has been answered previously, I just couldn't find it.

Thanks

[martindwilson]

not a good explanation,do you mean the lowest and highest of the 2 previous months only?