Formula Errors

How to Match Values Between Two Date Ranges in Excel

WPS Editor Sep 28, 2026 872 views

Question details

The user needs a formula to look up and return a value based on whether a specific target date falls between a given start date and end date.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Looking up and mapping data associated with specific date periods, where target dates must be compared against columns containing start and end dates.
Observed behavior: Lookup formulas fail to match the dates correctly because some cells contain text strings formatted to look like dates instead of actual Excel date serial numbers, and simply changing the cell format does not resolve the issue.

Before you start

Before applying any lookup formula, verify that your start, end, and target dates are stored as actual Excel date values rather than text strings, as this is the most common cause of date range matching errors.

Solution 1Recommended

Convert Text to Dates and Use the FILTER Function

Fix underlying data format issues using conversion tools, then apply a modern FILTER array formula to dynamically check if the target date falls between the start and end dates.

Excel cannot compare a real date to a text-formatted date. Changing the cell format from 'Text' to 'Date' via right-click does not convert the underlying data. You must first force the text into real date values before running your lookup.

Identify text-formatted dates

Click on an empty cell and use the formula =ISTEXT(A2) (assuming A2 is your date). If it returns TRUE, the date is stored as text.

Convert text to real dates

Highlight the column with text dates, go to the Data tab, and click 'Text to Columns'. Click 'Next' twice, select 'Date' in the Column data format section, choose the correct format (e.g., MDY), and click 'Finish'.

Apply the FILTER formula

Select the cell where you want the result. Type the formula: =FILTER(Result_Range, (Target_Date >= Start_Date_Range) * (Target_Date <= End_Date_Range), "No Match").

Evaluate the result

Press Enter. The boolean logic (using the asterisk for 'AND') will check both conditions and return the corresponding value from the Result Range.

Convert Text to Dates and Use the FILTER Function

Tip for Older Excel Versions: If you are using a version of Excel that does not support the FILTER function, you can use the SUMIFS function (for numeric results) or an INDEX and MATCH array formula.

Solution 2

Use SUMIFS to Return Numeric Values Between Dates

If the value you need to return is a number, the SUMIFS function provides a lightweight and backward-compatible way to find the value associated with a specific date range.

Advanced Spreadsheet Features

Look Up Date Ranges Easily in WPS Spreadsheet

WPS Spreadsheet offers full compatibility with advanced Excel array formulas, making it easy to process date ranges, convert text formats, and match complex values effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date ranges.
2. Clean your date data: Highlight your date columns, navigate to the Data tab, and use 'Text to Columns' to convert any text-based dates into true serial date values.
3. Enter the matching formula: Select your result cell and input your desired lookup formula, such as =FILTER(C2:C10, (F2>=A2:A10)*(F2<=B2:B10)).
4. Retrieve your data: Press Enter. WPS Spreadsheet will process the boolean array logic and instantly populate the matched value.

Fully compatible with Microsoft Excel date formats and advanced array formulas like FILTER and SUMIFS.Built-in error checking to instantly identify and resolve text-formatted dates.Intuitive Data tab tools like Text to Columns for rapid data cleaning.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't changing the number format fix my date errors?

Changing a cell's format from 'Text' to 'Date' via the Format Cells menu only alters how the data is visually presented; it does not change the underlying data type. You must re-enter the data, use the DATEVALUE function, or apply the Text to Columns tool to convert the text string into a recognizable date serial number that formulas can read.

Can I use VLOOKUP to match a target date between a start and end date?

Standard VLOOKUP is generally not suitable for matching a value between two distinct columns (start and end dates) unless the data is strictly structured for an approximate match using sorted thresholds. It is much more reliable and accurate to use FILTER, XLOOKUP, or an INDEX/MATCH array formula with boolean logic.

What happens if there are overlapping date ranges in my dataset?

If your start and end date ranges overlap and a target date falls into multiple ranges, lookup formulas like FILTER will return all matching values (which may cause a SPILL error depending on adjacent cell data). SUMIFS will add the overlapping values together. Ensure your date ranges are contiguous and non-overlapping for precise single-value lookups.

Excel & Data Problems