Retrieve a Value from a Selected Date Column in Excel
Question details
Find a formula that selects a column by its date heading and returns the corresponding value from another row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user selects or enters a date and wants to retrieve a value from the column with the matching date heading.
- Observed behavior
- The desired result is the value in the specified row under the selected date.
Identify the date-heading range, the row containing the values to return, and the cell containing the selected date. Make sure the headings and selected date are stored as matching Excel dates rather than text.
Use XLOOKUP to find the selected date column
Use this approach in Excel versions that support XLOOKUP, including Microsoft 365.
The example assumes date headings are in B1:G1, the values to return are in B2:G2, and the selected date is in J1. Adjust these ranges to match your worksheet.
Type or select the date to look up in cell J1.
Select the cell where you want the result and enter =XLOOKUP(J1,B1:G1,B2:G2,"Date not found").
Press Enter. Excel returns the value from row 2 under the matching date, or displays Date not found if there is no match.

Use INDEX and MATCH for a compatible date lookup
Use INDEX and MATCH when XLOOKUP is unavailable or you need a formula compatible with older Excel versions.
Look Up a Date Column in WPS Spreadsheet
Use INDEX and MATCH in WPS Spreadsheet to find a selected date heading and return the value beneath it.
- 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Choose the lookup date: Enter the date to find in J1, with date headings in B1:G1 and the values to return in B2:G2.
- 3. Add the lookup formula: Select an empty result cell and enter =INDEX(B2:G2,1,MATCH(J1,B1:G1,0)). Adjust the cell references to fit your data.
- 4. View the result: Press Enter to return the value under the matching date heading.

Frequently Asked Questions
How do I return a value from a different row?
Change the return range in the formula. For example, use B5:G5 instead of B2:G2 to return a value from row 5, while keeping the date headings in B1:G1.
Why does my date lookup return #N/A?
Check that the selected date matches a heading and that both are stored as dates, not text. A hidden time component can also prevent an exact match.
What happens if the same date appears in more than one column?
The examples return the value from the first matching date heading. Use unique headings if each date should identify one column.
Can I use a drop-down list to select the date?
Yes. In Excel, select J1 and choose Data > Data Validation > List, then use the date-heading cells as the list source. The lookup formula can reference J1.




