'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return a Number from an Excel Drop-Down List Selection
Question details
The user needs to assign and retrieve a specific numerical value corresponding to a text selection made from a data-validation drop-down list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Selecting a text item (like a name) from a drop-down list and needing an adjacent cell to automatically display its assigned numerical value.
- Observed behavior
- The goal state is to automatically return the correct number when a specific text item is selected from the drop-down menu, avoiding approximate match errors.
Ensure you have already created your drop-down list using Data Validation and set up a separate reference table that contains the text options and their corresponding numerical values.
Use the VLOOKUP Function for Exact Matching
The standard and most reliable method to return a corresponding number from a drop-down selection is to use the VLOOKUP function linked to your reference table.
The VLOOKUP function searches for a value in the first column of a table array and returns a value in the same row from another column. By setting the match type to FALSE or 0, you ensure Excel only returns a number if there is an exact match with your drop-down selection. The names do not need to be in any specific order when an exact match is used.
Create a reference table (for example, in cells C1:D4) with your drop-down text options in the first column and the corresponding numbers in the second column.
Click on the empty cell where you want the numerical value to automatically appear after a drop-down selection is made.
Type the formula =VLOOKUP(A2, C1:D4, 2, FALSE). In this formula, A2 is your drop-down list cell, C1:D4 is the reference table range, 2 is the column index containing the numbers, and FALSE enforces an exact match.
Press Enter on your keyboard. Test your drop-down list by selecting different names to verify that the target cell updates with the correct assigned number.
Easily Manage Drop-Down Lists and Formulas with WPS Spreadsheet
WPS Spreadsheet provides a fully compatible and intuitive environment for creating drop-down lists and executing VLOOKUP formulas. It handles complex data lookups flawlessly while remaining completely free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your reference table.
- 2. Create a drop-down list: Navigate to the Data tab on the top ribbon, select 'Validation', and set the criteria to 'List' to create your drop-down.
- 3. Insert the lookup formula: Use the built-in formula builder or type =VLOOKUP() directly into the adjacent cell to instantly map your numerical values.
Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error?
The #N/A error typically occurs if the selected name from your drop-down list does not exist in your lookup table, or if the lookup range in your formula hasn't been expanded to include newly added rows. Check that your lookup range fully covers all data.
Do the names in my reference table need to be in alphabetical order?
No. As long as you use FALSE or 0 as the fourth argument in your VLOOKUP formula, the software will look for an exact match. Because of this exact match setting, the names in your reference table do not need to be sorted alphabetically.
Can I use XLOOKUP instead of VLOOKUP for this task?
Yes, if you are using a supported version of Excel or WPS Office, you can use the XLOOKUP function. XLOOKUP is often easier because it defaults to an exact match and allows you to specify separate arrays for the lookup values and the return values, removing the need to count columns.
