Excel Power Query: Return Matching Values from a Two-Dimensional Date Array
Question details
The user needs to match related values against dates stored in a two-dimensional Excel range.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Dates must be consolidated into a sorted, searchable list while preserving the source row or identifier for matching related values.
- Observed behavior
- A standard XLOOKUP does not search the original two-dimensional date array as expected because the data has not been reshaped into a normalized list.
Convert the source range to an Excel table and confirm that each column has a clear header. Include a source row, record ID, or other identifier so it can be retained when the date columns are unpivoted.
Unpivot the date table with Excel Power Query before matching values
Use this method when dates are spread across multiple columns and need to become a normalized, sortable list with their original identifiers.
Power Query reshapes the two-dimensional table into a list by keeping identifier columns and converting date columns into attribute-value pairs. The resulting table can be sorted, filtered, matched, and refreshed without rebuilding the transformation manually.
Select any cell in the source table, open the Excel Data tab, choose From Table/Range, and confirm that the table has headers in the Power Query Editor.
In Power Query Editor, select the source row, record ID, or other columns that must remain attached to each date.
Right-click the selected identifier columns and choose Unpivot Other Columns. Power Query creates attribute and value columns containing the former date headers and their values.
Rename the generated columns if needed, set the date column to the Date data type, remove blank values with the filter menu, and sort the date column from oldest to newest.
Choose Home > Close & Load to return the normalized list to Excel. When the source table changes, use Data > Refresh All to rebuild the matching list.
Move normalized spreadsheet workflows to WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing familiar spreadsheet files. It supports common Excel formats, offers a familiar interface for seamless migration, and can handle normalized result tables after they are prepared.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and select Open to load the XLSX or XLS workbook.
- 2. Review the normalized result: Use the Data tools, filters, and sorting controls to inspect the date list and its associated identifiers.
- 3. Save in an Excel-compatible format: Choose Menu > Save As and select XLSX when you need to share the edited workbook with Microsoft Excel users.
Frequently Asked Questions
Why does XLOOKUP fail on a two-dimensional date range?
XLOOKUP is designed to search a single lookup array. When dates are distributed across multiple columns and rows, reshape the range with Power Query before matching values.
How do I preserve the original row when unpivoting dates?
Keep the source row, record ID, or other identifying columns selected in Power Query, then choose Unpivot Other Columns. Those columns remain attached to every generated date record.
Can I refresh the Power Query result after adding dates?
Yes. Add the new data within the source Excel table, then select Data > Refresh All. Power Query reruns the unpivot, cleanup, and sorting steps.
What should I do with blank date cells in the source table?
After unpivoting, open the filter for the generated date value column and exclude blank or null entries before loading the result back to Excel.
