Function Problems

Return a Date from an Excel Data Validation Drop-Down

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Return a date associated with a number selected from a drop-down list.

Return a Date from an Excel Data Validation Drop-Down
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a Data Validation list of numbers and a separate table containing the related dates.
Observed behavior
Selecting a number displays that number in the drop-down cell; the related date must be retrieved separately.
Before you start

Arrange the lookup numbers in one column and their corresponding dates in the next, such as D2:D26 and E2:E26. Make sure the dates are stored as actual dates and the lookup numbers match the values in the drop-down list.

Solution 1Recommended

Use a lookup formula beside the Excel drop-down

Keep the selected number in the validation cell and display its matching date in an adjacent cell.

1
Select a number

Use the Data Validation drop-down in A1 to select a number from the lookup table.

2
Enter a lookup formula

In B1, enter =XLOOKUP(A1,$D$2:$D$26,$E$2:$E$26,"Not found"). If XLOOKUP is unavailable, enter =IFERROR(VLOOKUP(A1,$D$2:$E$26,2,FALSE),"Not found") instead.

3
Format the result as a date

Select B1, then choose Home > Number Format > Short Date or another date format.

Use a lookup formula beside the Excel drop-down
The selection stays in A1: Data Validation does not replace the selected number with a related date in the same cell. The lookup formula returns the date in B1.
Work with drop-down lists in WPS

Return a matching date in WPS Spreadsheet

Use a Data Validation list to select a number, then use VLOOKUP in an adjacent cell to display its matching date.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook. Place lookup numbers in D2:D26 and matching dates in E2:E26.
  2. 2. Create the drop-down: Select A1, open Data > Data Validation, choose List, and set the source to =$D$2:$D$26.
  3. 3. Enter the lookup formula: Select B1 and enter =IFERROR(VLOOKUP(A1,$D$2:$E$26,2,FALSE),"Not found").
  4. 4. Format and test the date: Select B1 and choose a date format from Home > Number Format. Then select a number from the drop-down in A1.
Return dates associated with drop-down selectionsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceGet started with WPS Office for free
QA img-9

Frequently Asked Questions

Can Data Validation replace the selected number with its matching date?

No. A Data Validation list displays the selected value in the cell. Use a lookup formula in another cell to return the related date.

Why does the result appear as a number instead of a date?

Excel stores dates as serial numbers. Select the result cell and choose Home > Number Format > Short Date, provided the source value is an actual date.

What should I check if the formula returns Not found?

Check that the selected value matches an entry in D2:D26 and that numbers are not stored as text in one location but as numbers in the other. Also check the lookup ranges in the formula.

What happens if the lookup table contains duplicate numbers?

XLOOKUP and an exact-match VLOOKUP return the first matching date. Use unique lookup numbers if each selection should identify one specific date.