How to Use Excel Formula to Return All Matches Between Two Dates
Question details
The user needs a formula to extract and display all values that fall between two overlapping dates, as standard lookup functions only return a single match.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Matching items, such as birthdates to age groups, where date ranges overlap and multiple matching categories must be returned simultaneously.
- Observed behavior
- Traditional lookup functions like XLOOKUP and VLOOKUP only return the first matching result instead of retrieving every overlapping match in the dataset.
Ensure you are using a version of Excel (Microsoft 365, Excel 2021) or WPS Office that supports Dynamic Array functions like FILTER. Older versions do not support this formula out of the box.
Use the FILTER Function for Multiple Date Criteria
The FILTER function effortlessly extracts all matching records across overlapping date ranges by using Boolean logic to evaluate multiple conditions simultaneously.
Unlike VLOOKUP or XLOOKUP which stop at the first match, the FILTER function is specifically designed to return an array of all matches. By multiplying two conditions together (greater than the start date AND less than the end date), we create a strict logic statement that filters out non-matching rows.
Ensure your dataset is organized. For example, place your target categories in column A (A6:A11), start dates in column B (B6:B11), and end dates in column C (C6:C11). Select cell F3 to input the specific target date you want to look up.
Click on an empty cell where you want the list of matching results to appear. Type the formula: =FILTER(A6:A11,(B6:B11<=F3)*(C6:C11>=F3),"").
Press Enter on your keyboard. The formula will automatically spill down the column, displaying every category from column A where the target date in F3 falls exactly between the start and end dates.

Filter Date Ranges Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions, including FILTER, XLOOKUP, and SORT. You can easily manage complex overlapping date ranges and extract multiple data points without needing an expensive premium subscription.
- 1. Open your workbook in WPS: Launch WPS Office, select 'Spreadsheet' from the home screen, and open your existing Excel file containing the date ranges.
- 2. Apply the FILTER formula: Select an empty cell and enter the exact same FILTER formula used in Excel: =FILTER(A6:A11,(B6:B11<=F3)*(C6:C11>=F3),"").
- 3. View dynamic array results: Hit Enter. WPS Spreadsheet will calculate the multiple criteria and instantly spill all matching records into the adjacent cells, just like Excel.

Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
This error generally occurs when the FILTER function finds no matches and you haven't provided the optional [if_empty] argument. To fix it, simply add "" (empty quotes) at the end of your formula, for example: =FILTER(A1:A5, B1:B5=C1, "").
Can I use multiple criteria with the FILTER function in other scenarios?
Yes. You can combine multiple conditions by wrapping each logic statement in parentheses and multiplying them together, like (Condition1)*(Condition2). This acts as an 'AND' operator, meaning both conditions must be true for the data to be returned.
Is the FILTER function available in all versions of Excel?
No, the FILTER function is a dynamic array formula introduced in Microsoft 365 and Excel 2021. If you are using Excel 2019 or earlier, the formula will return a #NAME? error. In those older versions, you must use a complex array combination of INDEX, SMALL, IF, and ROW functions instead.




