Function Problems

Create a Dynamic Date Range for Lookup in Excel

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Create a lookup that uses a moving date range based on today or a selected date.

Create a Dynamic Date Range for Lookup in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set the start date

Enter a calendar date in A1, or enter =TODAY() to use the current date.

2
Display records in the period

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.

3
Look up a matching key

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").

Filter and look up records within a moving date range
First match and date boundaries: XLOOKUP returns the first matching row. To include the date exactly 90 days after A1 as well, change <A1+90 to <A1+91 in both formulas.
Dynamic date lookups

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. 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. 2. Set the start date: Enter a date or =TODAY() in cell A1.
  3. 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. 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")).
Update the lookup window automatically with TODAY().Find a matching key within the selected date period.Open and edit Microsoft Excel XLSX files.Use familiar spreadsheet formulas and a lightweight interface.
microsoft office alternative - wps office

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.