How to create drop down list but show different values in Excel?
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
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:
Demo: Create drop down list but show different values in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
