Skip to main content

Kutools for Office ā€” One Suite. Five Tools. Get More Done.

How to create drop down list but show different values in Excel?

Author Xiaoyang Last modified

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.

dropdown show different values

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:

click Data > Data Validation > Data Validation

3. In the "Data Validation" dialog box, under the "Settings" tab, choose "List" from the "Allow" drop down, and then click select button button to select the "Name" list which you want to use as drop down values in the "Source" text box. See screenshot:

specify options in the dialog box

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

select view code and paste the code into the module

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:

when you select an item from the drop down list, a relative different value is displayed

Advanced Drop down list of Kutools

"Kutools for Excel" takes drop-down lists to the next level with its "Advanced Drop-Down List" features, offering powerful customization options that go beyond standard Excel functionality. With this tool, you can create:

Kutools for Excel provides 300+ advanced features to simplify complex tasks and improve creativity and efficiency. Now, Kutools integrates AI to make data processing more intelligent and automated.

  • Multi-Select Drop-Down List

    Allow users to select multiple options in a single cell, making data entry flexible.

  • Drop-down List with checkboxes

    Add checkboxes to drop-down menus for intuitive selection, ensuring clarity and accuracy.

  • Make Dropdown list Searchable, Auto-popup

    Easily search and filter options within the drop-down, perfect for lists with numerous items.

    Demo: Create drop down list but show different values in Excel

     

    Best Office Productivity Tools

    šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functionsā€¦
    Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
    Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
    Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
    Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
    Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
    Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
    Use Kutools in your preferred language ā€“ supports English, Spanish, German, French, Chinese, and 40+ others!

    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!

    All Kutools add-ins. One installer

    Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

    Excel Word Outlook Tabs PowerPoint
    • All-in-one suite ā€” Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
    • One installer, one license ā€” set up in minutes (MSI-ready)
    • Works better together ā€” streamlined productivity across Office apps
    • 30-day full-featured trial ā€” no registration, no credit card
    • Best value ā€” save vs buying individual add-in