Finding the last date with data

[rickrawfl44]

In the attached example spread I'm looking to use an offset for any of the dates in column A to find a price in the reference table corresponding to the day before said date in 'A' that actually exists.

The price for 1/1/2007 actually settles the last day the product was traded, so it would need to be choose the price (in the ref table) on the 29th of December since that was the last day it actually traded.

I can't figure out how to work in the weekday function which was the only idea I'd had so far in order to do the date check.

[NBVC]

I assume you want the last price of the day too....

Try:

=LOOKUP(2,1/($A$2:$A$44=K2-1),$D$2:$D$44)

adjusting ranges as needed

and copied down