Formula Errors

How to Return Values for Matching Date Ranges in Excel

WPS Editor Sep 27, 2026 869 views

Question details

The user needs an Excel formula to identify which date range contains a specific effective date and return the corresponding value from a designated column.

How to Return Values for Matching Date Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting corresponding data across columns based on whether a specific effective date falls within dynamically checked date ranges.
Observed behavior: The user wants to construct a dynamic formula utilizing functions like LET, DATEVALUE, and IF to match date bounds and output values accurately across comparable columns.

Before you start

Ensure your effective dates are formatted correctly as actual date values in Excel, and verify that your target range headers or bounds are recognizable by Excel's date functions rather than just plain text.

Solution 1Recommended

Use the LET and IF Functions to Match Date Ranges

This solution combines LET, DATEVALUE, IF, and AND to dynamically determine if an effective date falls within a specific month's range and returns the appropriate value.

The LET function simplifies the formula by assigning a name to the calculation result, improving readability and preventing redundant calculations. In this case, we use it to define the start date and check if it aligns with the effective date range.

Select the starting output cell

Click on the cell where you want the first matched value to appear (for example, F9).

Enter the nested date formula

Input the formula =LET(d,DATEVALUE("1-"&$B9),IF(AND(d<=$C$4,d>F$6-DAY(F$6)),$D9,"")) into the formula bar and press Enter.

Fill the formula down and across

Select the cell with the applied formula, click and hold the fill handle at the bottom right corner, and drag it down for the remaining rows, then drag it across to apply it to comparable columns.

Use the LET and IF Functions to Match Date Ranges

Formula Breakdown: The 'd' variable is assigned the parsed date using DATEVALUE. The IF statement then checks if 'd' is less than or equal to the effective date in C4, and greater than the end of the previous month. If both conditions are true, it returns the value from column D.

Solve Date Match Formulas Easily

Process Date Range Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions like LET, DATEVALUE, and IF, allowing you to seamlessly calculate date ranges and extract data without any compatibility issues.

1. Open your file in WPS Office: Launch WPS Office and open your spreadsheet document containing the date ranges and effective date.
2. Enter the matching formula: Click on the target output cell and input the provided LET and IF combination formula.
3. Drag to fill the range: Use the fill handle at the bottom right of the active cell to drag the formula across your desired rows and columns to apply the logic instantly.

Fully compatible with Microsoft Excel formulas, cell references, and date formatting.Supports modern functions like LET and dynamic arrays for complex date range matching.Lightweight software that processes heavy datasets and complex formulas smoothly.Free and user-friendly interface that ensures a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the DATEVALUE function returning a #VALUE! error?

This happens if the text string provided to DATEVALUE cannot be recognized as a valid date format by your system's regional settings. Ensure the concatenated text accurately represents a recognizable date format.

Can I use this formula in older versions of Excel?

The LET function is available in Microsoft 365, Excel 2021, and modern WPS Office versions. If you are using an older version of Excel, you will need to replace the 'd' variable with the actual DATEVALUE calculation repeated inside the IF function.

How do I adjust the formula if my effective date is in a different cell?

Simply replace the absolute reference $C$4 in the formula with the new cell address containing your effective date, ensuring you keep the dollar signs if the reference should remain locked when copying the formula across other cells.

Excel & Data Problems