There are two methods;
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/2019 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(A5,$E$5:$F$8,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.
If you don’t see Developer tab in Excel ribbon, follow this link;
The steps in Excel 2010/2019 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
Add to favorites