Function Problems

Retrieve a Value from a Selected Date Column in Excel

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Find a formula that selects a column by its date heading and returns the corresponding value from another row.

Retrieve a Value from a Selected Date Column in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the selected date

Type or select the date to look up in cell J1.

2
Enter the lookup formula

Select the cell where you want the result and enter =XLOOKUP(J1,B1:G1,B2:G2,"Date not found").

3
Check the result

Press Enter. Excel returns the value from row 2 under the matching date, or displays Date not found if there is no match.

Use XLOOKUP to find the selected date column
Adapt the example: Use the actual heading and return ranges in your workbook. If a date appears to match but is not found, check whether one cell contains text or a time component.
Work with date lookups in WPS

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. 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 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. 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. 4. View the result: Press Enter to return the value under the matching date heading.
Retrieve a value from a selected date column with a formulaOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.