Dynamic date range for LOOKUP

Copper Contributor

I would like to be able to look up small values based on a moving calendar.  

I am able to identify today, the future date, and the cells which store the future date (or closest date to future date).  What I have been doing is to look up the SMALL value in a range that I manually enter.  Then I look up and return the date that corresponds to that small value using XLOOKUP.

I can use the TODAY() function, and add 90 days to it, and use an ADDRESS(MATCH) to return the cell.  Is there a way that I can put this in one formula so that the date entered (usually TODAY() but I may need to reference a specific calendar date as well) can dynamically update my range?      

0 Replies