How to Use Excel Formulas to Match Date Ranges and Return Titles
Question details
The user needs to find an Excel formula that identifies which predefined date range fully contains a specified lookup date range and returns its associated title.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing lookup start and end dates against a table of established start and end dates to find the containing period and retrieve its name.
- Observed behavior
- Requires a dynamic array formula approach to output the correct title when both the start date and end date conditions are satisfied.
Ensure your dataset is organized into clear columns for established start dates, end dates, and titles, and verify that all date cells are formatted correctly as Dates.
Use the FILTER Function for a Single Date Range
The FILTER function is ideal for extracting a title when a single lookup date range falls entirely within an established date range.
This approach uses boolean logic to check two conditions simultaneously: the established start date must be less than or equal to the lookup start date, and the established end date must be greater than or equal to the lookup end date.
Click on the cell where you want the matched title to appear.
Enter the formula: =FILTER($C$2:$C$5,($A$2:$A$5<=F4)*($B$2:$B$5>=G4),""). Assume A contains start dates, B contains end dates, C contains titles, F4 is your lookup start date, and G4 is your lookup end date.
Press Enter. The cell will display the matching title or remain blank if no date range matches.
Apply MAP and LAMBDA for Multiple Lookup Rows
If you have a list of multiple lookup date ranges, combining MAP, LAMBDA, and FILTER allows you to evaluate them all dynamically without dragging formulas down.
Use WPS Spreadsheet for Advanced Formula Processing
WPS Spreadsheet fully supports advanced array functions like FILTER, MAP, and LAMBDA, making it easy to match complex date ranges and extract data seamlessly.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your date ranges.
- 2. Organize Your Data: Ensure your established date ranges and lookup dates are in clearly defined, separate columns.
- 3. Apply the FILTER Function: Use the =FILTER() function in an empty cell to cross-reference the start and end dates just as you would in standard spreadsheet software.
- 4. View Matched Titles: Press Enter to instantly process the array and display the correct matching titles.
Frequently Asked Questions
What happens if the lookup date range overlaps two established date ranges?
If the lookup dates satisfy the conditions of multiple established date ranges, the FILTER function will return multiple titles in a spilled array. Ensure your established ranges do not overlap if you only want a single result.
Why does my FILTER function return a #CALC! error?
A #CALC! error typically means the FILTER function couldn't find any established date range that fully contains your lookup dates. Adding ,"" at the end of the FILTER formula prevents this error by returning a blank cell instead.
Can I match date ranges without using the FILTER function?
Yes, if you are using an older version of your spreadsheet software that does not support dynamic arrays, you can use an alternative formula combining INDEX and MATCH with boolean logic, or SUMPRODUCT to calculate the matching row.
