Create an Excel Dropdown with Descriptions and Numeric Values
Question details
Create a dropdown that shows a description and its kilometer value while retaining the numeric value for calculations.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to select a route from a data validation list and use its corresponding distance in calculations.
- Observed behavior
- The dropdown should display both the route description and distance, while the stored result is a numeric kilometer value.
Enter route descriptions in E6:E8 and their numeric kilometer values in F6:F8. Make sure each combined dropdown entry is unique and the values in column F are numbers.
Use VBA to replace the selected dropdown entry with its numeric value
Use this method in desktop Excel when the dropdown cell itself must contain only the kilometer value.
An Excel data validation list stores the selected source text; it cannot independently display a label and store a different value. A worksheet event macro can replace the selected text with the corresponding number after selection.
In G6, enter =E6&" : "&F6&" km" and fill the formula down through G8. The cells will show entries such as Route A : 20 km.
Select A2, open Data > Data Validation, choose List, and set Source to =$G$6:$G$8. On the Error Alert tab, clear Show error alert after invalid data is entered, then select OK.
Right-click the worksheet tab, choose View Code, and paste this code into that sheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim idx As Variant On Error GoTo HandleError If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub If IsEmpty(Target.Value2) Then Exit Sub idx = Application.Match(Target.Value2, Me.Range("G6:G8"), 0) If IsError(idx) Then Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True MsgBox "Select an entry from the dropdown." Exit Sub End If Application.EnableEvents = False Target.Value2 = Me.Cells(5 + CLng(idx), "F").Value2 Application.EnableEvents = True Exit Sub HandleError: Application.EnableEvents = True MsgBox "Could not update the dropdown value: " & Err.Description End Sub
Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros when prompted, then select an entry in A2; the cell should change to its numeric kilometer value.

Return the numeric value in a separate cell with a formula
Use this macro-free method when the dropdown can retain its descriptive text and the numeric value can appear in another cell.
Create a descriptive dropdown and numeric lookup in WPS Spreadsheet
Use a data validation list to show each route and distance, then return the numeric kilometer value in a separate cell with a lookup formula.
- 1. Enter the source data: In WPS Spreadsheet, enter route descriptions in E6:E8 and numeric kilometer values in F6:F8.
- 2. Combine the labels: In G6, enter =E6&" : "&F6&" km" and fill the formula down through G8.
- 3. Add the dropdown: Select A2, open Data > Data Validation, choose List, and use =$G$6:$G$8 as the source.
- 4. Return the numeric value: Enter =IFERROR(INDEX($F$6:$F$8,MATCH(A2,$G$6:$G$8,0)),"") in B2, then choose a route in A2.

Frequently Asked Questions
Can an Excel validation list display a description but store a different value in the same cell?
Not by itself. Data validation stores the selected source text. Use a worksheet VBA macro to replace it with the numeric value, or use a formula in a separate cell.
Why does the lookup formula return a blank?
Check that A2 contains an entry from G6:G8 and that the descriptions in the source range are unique. The IFERROR formula returns a blank when it cannot find a match.
Will the VBA method work if macros are disabled?
No. Save the file as an .xlsm workbook and enable macros in desktop Excel when prompted. Without the macro, the selected text remains in the cell.
Can I show a description without adding it to every dropdown entry?
Yes. Select the validated cell, open Data > Data Validation, and use the Input Message tab to show guidance when the cell is selected. The message is not stored as the selected value.




