Excel dropdown with name/value pairs

Simple!
Here is what we are going to get!

3 Steps Only:

1. Define a range to use as the lookup value

2. Create the dropdown list

3. Paste in some code

Step 1: Setup Sheet2 like this and define a Named Range as _descrLookup:

( Highlight -> Right-Click -> "Define Name..." )

This is an optional step, but it just makes it easy to follow for Step 3.

Step 2: In Sheet1, create the dropdown using Data Validation and use the VALUES YOU WANT TO BE SHOWN IN THE DROPDOWN as the source. In this example it’s Sheet2 A2:A4 (see above image):

( Data -> Data Validation )

Step 3: Add some VBA code to Sheet1:

( Right-Click the tab Sheet1 -> View Code )

Paste this into the code window for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    selectedVal = Target.Value

    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedVal, Worksheets("Sheet2").Range("_descrLookup"), 2, False)

        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If

    End If
End Sub