Return a Date from an Excel Data Validation Drop-Down
Question details
Return a date associated with a number selected from a drop-down list.

- 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.
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.
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.
Use the Data Validation drop-down in A1 to select a number from the lookup table.
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.
Select B1, then choose Home > Number Format > Short Date or another date format.

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. 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. Create the drop-down: Select A1, open Data > Data Validation, choose List, and set the source to =$D$2:$D$26.
- 3. Enter the lookup formula: Select B1 and enter =IFERROR(VLOOKUP(A1,$D$2:$E$26,2,FALSE),"Not found").
- 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.

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.




