Select a cell in a row 30 days prior to today's row

This is a simple concept, but I'm at a loss.

I have a list of dates in column A and stock prices in Column B, both columns grow every day the stock market is open.
I am looking for a VBA function that will go 30 days prior to today's date and give me the price in column B on that date. I can't just go 30 rows up, because there are gaps for weekends, holidays, and days I may have missed entering data. Also, if there is no exact match for 30 days, it should use the next oldest day closest to 30 days.

Any help?

Have you tried the lookup funtion. something like this where column C is the dates and Column D is the value

Please Login or Register  to view this content.