How to Create an excel dropdown list that displays text with a numeric hidden value

Question

I am trying to create a drop down list that displays text with a hidden numerical value attached. Then I will have a standard formula on the same row that calculates a value based upon the hidden value selected.

Answer 1

Data validation drop down

There is a list option in Data validation. If this is combined with a VLOOKUP formula you would be able to convert the selected value into a number.

The steps in Excel 2010 are:

• Create your list with matching values.
• On the Data tab choose Data Validation
• The Data validation form will be displayed
• Set the Allow dropdown to List
• Set the Source range to the first part of your list
• Click on OK (User messages can be added if required)

In a cell enter a formula like this

=VLOOKUP(A2,$D$3:$E$5,2,FALSE) 

which will return the matching value from the second part of your list.

Form control drop down

Alternatively, Form controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.

The steps in Excel 2010 are:

• Create your list of data in a worksheet
• Click on the Developer tab and dropdown on the Insert option
• In the Form section choose Combo box or List box
• Use the mouse to draw the box on the worksheet
• Right click on the box and select Format control
• The Format control form will be displayed
• Click on the Control tab
• Set the Input range to your list of data
• Set the Cell link range to the cell where you want the number of the selected item to appear
• Click on OK