Create a Dynamic Date Range for Lookup in Excel
Question details
Create a lookup that uses a moving date range based on today or a selected date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Find records or return a matching value within a period such as 90 days.
- Observed behavior
- The lookup range should update when the start date changes or the current date advances.
Make sure the date column contains actual Excel dates rather than text. The examples use dates in B2:B1000, lookup keys in C2:C1000, and return values in D2:D1000.
Filter and look up records within a moving date range
Use FILTER to display matching records and XLOOKUP to return the first matching value in supported Excel versions.
These examples cover 90 calendar days starting with the date in A1. They include the start date and exclude the date 90 days later.
Enter a calendar date in A1, or enter =TODAY() to use the current date.
In an empty cell such as H2, enter =FILTER(B2:D1000,(B2:B1000>=A1)*(B2:B1000<A1+90),"No matching dates"). Leave space below and to the right for the results to spill.
Enter the lookup key in F1. In an empty cell, enter =XLOOKUP(1,(C2:C1000=F1)*(B2:B1000>=A1)*(B2:B1000<A1+90),D2:D1000,"Not found").

Use a helper column for lookup in older Excel versions
Use INDEX and MATCH with a helper column when FILTER or XLOOKUP is unavailable.
Build a moving date lookup in WPS Spreadsheets
Use a start date, a helper column, and INDEX and MATCH in WPS Spreadsheets to find the first matching record within a 90-day window.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets. Place dates in B2:B1000, keys in C2:C1000, and return values in D2:D1000.
- 2. Set the start date: Enter a date or =TODAY() in cell A1.
- 3. Create the helper column: In E2, enter =IF(AND(B2>=$A$1,B2<$A$1+90),C2,"") and fill it down through E1000.
- 4. Look up the value: Enter a nonblank key in F1. In G1, enter =IF(F1="","Enter a lookup value",IFERROR(INDEX(D2:D1000,MATCH(F1,E2:E1000,0)),"Not found")).

Frequently Asked Questions
How do I make the range start on today's date?
Enter =TODAY() in A1 and refer to A1 in the date criteria. The window moves as the workbook recalculates.
Does the 90-day range include the start date?
Yes. The criteria >=A1 and <A1+90 include the start date and the following 89 calendar dates. Use <A1+91 to include the date 90 days after the start date.
Why does the lookup return the wrong duplicate?
XLOOKUP and MATCH return the first eligible match in worksheet order. Sort the records by date first if you want the earliest eligible occurrence.
What if FILTER or XLOOKUP is not available?
Use the helper-column method with IF, INDEX, and MATCH. Enter the helper formula in E2 and fill it down alongside your data.
Why are some dates missing from the results?
Check that the cells in B2:B1000 contain real spreadsheet dates rather than text, and confirm that they fall on or after A1 and before A1+90.




