In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.
Create drop down list but show different value in drop down list cell
To finish this task, please do with the following step by step:
1. Create a range name for the cell values you want to use in the drop down list, in this example, I will enter the name dropdown in the Name Box, and then press Enter key, see screenshot:
2. Then select cells where you want to insert the drop down list, and click Data > Data Validation > Data Validation, see screenshot:
3. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down, and then click 
button to select the Name list which you want to use as drop down values in the Source text box. See screenshot:
4. After inserting the drop down list, please right click active sheet tab, and select View Code from the context menu, and in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:
VBA code: Display different value from the drop down list:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
selectedNa = Target.Value
If Target.Column = 5 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
Note: In the above code, the number 5 within If Target.Column = 5 Then script is the column number that your drop down list located, , the “dropdown” in this selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False) code is the range name you have created in step 1. You can change them to your needed.
5. Then save and close this code, now, when you select an item from the drop down list, a relative different value is displayed in the same cell, see screenshot:
