Find the Closest Date Within 45 Days in Excel or Power Query
Question details
Create a result table that matches every date in one source table with the closest date and related values from a second table when the date difference is no more than 45 days.

- Product
- Microsoft Excel and Power Query
- Device & OS
- not provided
- Scenario
- Two tables contain dates and related fields, and each record in the first table needs a closest-date match from the second table within a 45-day limit.
- Observed behavior
- The desired result is a refreshed table containing the closest eligible date and its corresponding values for each source record.
Confirm that both source ranges are formatted as Excel tables and that their date columns contain real date values rather than text. Identify the key columns and decide how ties should be handled when two dates have the same minimum difference.
Use Power Query to return the closest match within 45 days
Use this approach for repeatable matching across larger tables or whenever the source data will be updated and refreshed regularly.
Power Query can compare the dates from both tables, calculate an absolute day difference, remove matches beyond 45 days, and keep the row with the smallest remaining difference for each source record.
Click inside the first Excel table, open Data > From Table/Range, and choose Close & Load To > Only Create Connection. Repeat the process for the second table.
In Excel, open Data > Get Data > Combine Queries > Merge Queries as New. Select the two table queries and match any shared key columns, or create a comparison structure that allows each first-table row to be evaluated against the second table.
In Power Query Editor, choose Add Column > Custom Column and calculate the absolute difference between the source date and candidate date, such as Number.Abs(Duration.Days([SourceDate] - [CandidateDate])).
Use the difference column filter to keep values less than or equal to 45. Group by the source ID and source date, sort each group by the difference in ascending order, and keep the first row before expanding the corresponding values.
Select Home > Close & Load To and load the result to a worksheet. After changing either blue source table, right-click the result table and select Refresh.
Use a Microsoft 365 dynamic-array formula for the closest date
Use this option for a formula-based result when the workbook runs in Excel for Microsoft 365 or Excel for the web and the tables follow a consistent layout.
Try WPS Office for Excel-Compatible Date Matching Workbooks
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing Excel-compatible workbooks. Move your existing .xlsx files into a familiar spreadsheet interface, review date-matching formulas, and collaborate without changing your established workflow.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the desktop or Start menu.
- 2. Open the Excel workbook: Choose Menu > Open and select the existing .xlsx workbook containing the source and result tables.
- 3. Review the matching worksheet: Use the worksheet tabs, formula bar, and table data to inspect the date columns and update compatible spreadsheet formulas.
- 4. Save an Excel-compatible copy: Choose Menu > Save As, select Excel 2007-365 Workbook (*.xlsx), and save the updated file for continued sharing.

Frequently Asked Questions
How do I make Power Query return only dates within 45 days?
After calculating the absolute date difference in Power Query Editor with Add Column > Custom Column, open the difference column filter and keep values less than or equal to 45 before grouping and selecting the smallest difference.
What happens when no date is within the 45-day limit?
The source record will not have an eligible match after filtering. You can preserve it by using a left outer merge and replacing the missing expanded values with a label such as No match.
Can the closest date formula work in older versions of Excel?
The provided formula uses REDUCE, WRAPROWS, CHOOSEROWS, and other dynamic-array functions available in Microsoft 365 and Excel for the web. Older Excel versions may require helper columns, array formulas, or Power Query.
How do I update the result after adding rows to the source tables?
If the sources are formatted as Excel tables, add the new records directly below the existing table and right-click the Power Query result table, then select Refresh. For formulas, the table references usually expand automatically.
How are ties handled when two dates are equally close?
XLOOKUP returns the first occurrence of the minimum difference in the lookup range. Sort the candidate table first or add a secondary sorting rule in Power Query when a specific tie-breaking result is required.




