Excel - find last value in column within current date

Question

I have created the following table for tracking version releases for a web and app project, listing current and future release dates and versions for the platform.

I want the top row to automatically select the value furthers down it's respective column, but only when the date in column A is equal to or less than todays date.

The formula I had been using for row 2 previously looked like this:

=LOOKUP(2,1/(1-ISBLANK(B:B)),B:B)

This formula works well to find the last value in the column, but I need to somehow add in logic that ensures it shows the latest version build released to date.

Any help is greatly appreciated! Thanks in advance.

Answer 1

Define a dynamic named range (as a Name via Formulas ribbon | Define Name). Use a formula like:

=OFFSET($A$3,1,0,COUNTIF($A$3:$A$1000,"<="&TODAY()))

You can check that it's correct by selecting it from the Name Manager and you'll see the selected cells representing this range of dates <= today's date:

Then, add another Name for browser (and repeat for iOS, etc.) like:

=OFFSET(released_dates,0,1)

Then, instead of using the entirety of column B in your Lookup formula, you can refer to the named range browser_releases!

=LOOKUP(2,1/(1-ISBLANK(browser_releases)),browser_releases)