Function Problems

How to Merge Excel Tables by Date Range Without Power Query

Huda Qurayshi Oct 10, 2026 869 views

Question details

The user wants to merge a calendar date table with a transactional quantity table by matching dates that fall between specific start and end limits without relying on Power Query or Power Pivot.

How to Merge Excel Tables by Date Range Without Power Query

Product: Excel
Device & OS: not provided
Scenario: Filtering and matching data across two tables where a quantity applies over a specified date range, requiring the results to automatically spill using dynamic array functions.
Observed behavior: Attempting to create a spilled array to distribute quantities across dates triggers a #CALC! error when the function returns an empty array for dates with no matches.

Before you start

Ensure you are using a version of Excel or WPS Office that supports dynamic array functions, as the LET and FILTER formulas require modern array calculation engines to spill results automatically.

Solution 1Recommended

Use LET and FILTER Functions to Merge Tables

Utilize modern dynamic array functions to construct a custom formula that filters records by start and end dates, automatically spilling the calculated results.

By nesting the FILTER function inside a LET statement, you can define variables for your calendar dates and quantity table ranges. This keeps your formula clean and prevents you from having to use Power Query. To prevent calculation errors on dates with no corresponding quantity records, you must handle empty arrays.

Define your data variables using LET

Select the top-left cell where you want the merged data to appear. Type =LET( to begin your formula. Define variables for your Calendar Dates, Start Dates, End Dates, and Quantities.

Apply the FILTER function for date ranges

Within the LET function, write a FILTER logic that checks if the calendar date is greater than or equal to the Start Date array, and less than or equal to the End Date array (e.g., (StartDate<=Date)*(EndDate>=Date)).

Handle the #CALC! empty array error

Add an if_empty argument to your FILTER function, such as "No Match" or 0. This ensures that if a specific calendar date has no overlapping date range in the quantity table, the formula returns a blank or zero instead of breaking with a #CALC! error.

Calculate distributed quantities

If a quantity applies over several days, divide the total quantity by the number of days in the range (End Date - Start Date + 1) within the output step of your LET formula.

Use LET and FILTER Functions to Merge Tables

Automatic Spilling: Because LET and FILTER are dynamic array functions, pressing Enter will automatically spill the results down and across adjacent cells. Ensure those cells are empty to avoid a #SPILL! error.

Efficient Data Handling

Use WPS Spreadsheet to Handle Dynamic Arrays

WPS Spreadsheet fully supports advanced dynamic array functions like LET, FILTER, and SEQUENCE. You can effortlessly merge and manipulate complex date-range tables while maintaining perfect compatibility with your existing Excel files.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the calendar and quantity tables.
2. Select the target cell: Click on the first empty cell where you want your new merged table to begin spilling.
3. Enter the dynamic formula: Type your combined =LET(...) and FILTER formula, ensuring you include the if_empty parameter to handle dates without matches.
4. Calculate and format: Press Enter to execute the formula. The data will spill automatically, and you can apply date or number formatting directly from the Home tab.

Native support for modern dynamic array functions like LET and FILTERSeamless format compatibility with Microsoft Excel (.xlsx) formulasLightweight, fast performance even when calculating large spilled arraysFree and intuitive interface for everyday data processing tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #CALC! error when using the FILTER function?

The #CALC! error occurs when the FILTER function returns an empty array, meaning no rows in your data match the provided date range criteria. You can easily fix this by adding a fallback value in the 'if_empty' argument of the FILTER function, such as "" (blank) or 0.

How do I divide a quantity evenly across the days in a date range?

To distribute a total quantity evenly, calculate the total number of days in the range by subtracting the start date from the end date and adding 1. In your formula, simply divide the total quantity by this calculated number of days.

Can I use VLOOKUP or XLOOKUP instead of FILTER for date ranges?

Standard lookup functions like VLOOKUP and XLOOKUP are designed to find a single exact or closest match. When dealing with date ranges where you need to check if a date falls between two separate columns and potentially return multiple matching rows, combining FILTER with boolean multiplication is the most effective approach.

What does "spill automatically" mean in this context?

Spilling occurs when a dynamic array formula produces multiple results, and those results automatically fill into the adjacent empty cells down or across the worksheet. If any cell within that required spill area contains data, Excel will throw a #SPILL! error until the space is cleared.

Excel & Data Problems