How to Display Data from a Date-Based Column in Excel Using XLOOKUP
Question details
Return data from the column whose date header matches a selected date, so a roster or planning sheet can show the correct daily values without VBA.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a matrix or roster table with dates in a header row, such as Matrix!C1:HK1, and wants a selected date on a Roster sheet to automatically display the matching column values.
- Observed behavior
- The goal is for the Roster sheet to show values from the matching date column using lookup formulas such as XLOOKUP or INDEX MATCH rather than manual lookup or VBA.
Confirm that the dates in the header row and the selected date cell are true Excel dates, not text. Use =ISNUMBER(B1) or =ISNUMBER(Matrix!C1) to verify, and make sure all date columns and employee rows are included in the lookup ranges.
Use XLOOKUP to Return the Date-Matched Column or a Specific Employee Row
Best for WPS Spreadsheets and Excel versions that support XLOOKUP; it directly matches the selected date and can return a whole column or a single employee value.
XLOOKUP searches a lookup array for an exact date and returns the corresponding value or range from a return array. If the return array spans multiple rows, the formula can spill the matching column across the roster area.
In WPS Spreadsheets, open your workbook and click the cell on the Roster sheet where the date-matched data should appear.
Type =XLOOKUP($B$1,Matrix!$C$1:$HK$1,Matrix!C9:HK40) and press Enter. This matches the date in Roster!B1 against the date headers in Matrix!C1:HK1 and returns the corresponding column values.
To display only one employee's value, nest another XLOOKUP for the employee name. For example: =XLOOKUP($B$1,Matrix!$C$1:$HK$1,XLOOKUP($A2,Matrix!$B$9:$B$40,Matrix!$C$9:$HK$40)).
Confirm the returned value changes when you update the date in Roster!B1. If you see #N/A, check that both date cells are true dates and are formatted consistently.
Use INDEX and MATCH for Date-Based Column Lookups in Older Excel Versions
Use this formula when XLOOKUP is not available or when you need a non-spilling formula that works in more Excel versions.
Use HLOOKUP with MATCH to Display a Date-Matched Row Value
Use when your date headers are in a top row and you want a compact formula to return one employee's value for the selected date.
Create Date-Based Roster Lookups in WPS Spreadsheets Without VBA
Use WPS Spreadsheets to open your Excel workbook and apply XLOOKUP or INDEX MATCH so the Roster sheet automatically shows the column that matches the selected date. WPS preserves Excel formulas and .xlsx compatibility while offering a free, familiar spreadsheet environment.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click the File menu or open panel, and select the Excel workbook that contains the Matrix and Roster sheets.
- 2. Click the Roster sheet date cell: Go to the Roster sheet, select cell B1, and type or verify the date that should drive the lookup.
- 3. Enter the date-match formula: In the result cell, enter =XLOOKUP($B$1,Matrix!$C$1:$HK$1,Matrix!C9:HK40) or the INDEX MATCH version for your Excel version.
- 4. Save and verify the result: Press Enter, change the date in B1 to confirm the values update, then save the file as .xlsx to keep Excel compatibility.
Frequently Asked Questions
Why does my XLOOKUP date formula return #N/A?
The selected date and the header dates may not both be true Excel dates. Select each date cell and check =ISNUMBER(cell); if either returns FALSE, re-enter the date as a real date, not text, and ensure there is no time component causing an exact-match failure.
Can I display the entire date column instead of one value?
Yes. Use XLOOKUP with a return array that spans multiple rows, such as =XLOOKUP($B$1,Matrix!$C$1:$HK$1,Matrix!C9:HK40). In supported versions this spills the matching column into the adjacent cells.
How do I return data for a specific employee and date?
Combine a column lookup with an employee lookup. For example, nest XLOOKUP for the employee name inside the date XLOOKUP, or use INDEX MATCH with one MATCH for the employee row and one MATCH for the date column.
Is there a formula if my Excel version does not have XLOOKUP?
Use INDEX and MATCH: =INDEX(Matrix!$C$9:$HK$40,MATCH($A2,Matrix!$B$9:$B$40,0),MATCH($B$1,Matrix!$C$1:$HK$1,0)). This works in older Excel versions and in WPS Spreadsheets.
