Map Excel Drop-Down Values to Numbers with a Lookup Formula
Question details
Return a corresponding numeric rate when a user selects a text value from an Excel drop-down list.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a data validation drop-down to select a status and needs to display its associated rate in another cell.
- Observed behavior
- The selected text should be matched against a two-column table, with the corresponding number returned in the Rate cell.
Identify the cell containing the drop-down selection and choose an empty area for a two-column table of statuses and numeric rates. The example below uses B2 for the selection, C2 for the result, and G2:H6 for the mapping table.
Use VLOOKUP to Return the Rate for a Drop-Down Selection
Use an exact-match lookup to display the number associated with the selected status.
Enter each drop-down status in G2:G6 and its corresponding numeric rate in H2:H6. Make sure the status text matches the drop-down choices.
Select B2 and choose Data > Data Validation. Confirm that the list contains the statuses in your mapping table, or set the list source to =$G$2:$G$6.
Select C2 and enter =IFERROR(VLOOKUP(B2,$G$2:$H$6,2,FALSE),""). Select a status in B2 to display its rate in C2.
Drag the fill handle from C2 down the Rate column for additional rows. The dollar signs keep the mapping table fixed while the selection reference changes.

Map Drop-Down Selections to Rates in WPS Spreadsheets
Create a status-to-rate table and use the same VLOOKUP formula in WPS Spreadsheets to return a number for each selection.
- 1. Open your worksheet: Open the spreadsheet in WPS Spreadsheets and identify the drop-down cell, such as B2.
- 2. Enter the mapping: Type the status choices in G2:G6 and their numeric rates in H2:H6.
- 3. Add the formula: Select C2 and enter =IFERROR(VLOOKUP(B2,$G$2:$H$6,2,FALSE),"").
- 4. Test and fill down: Choose a status in B2, check the rate in C2, and drag C2's fill handle down for other rows.

Frequently Asked Questions
Why does the formula return a blank?
IFERROR hides lookup and other formula errors. Check that the selected text in B2 matches an entry in G2:G6 and that the formula references the correct table.
Can the mapping table be on another worksheet?
Yes. Include the sheet name in the range, for example: =IFERROR(VLOOKUP(B2,'Rates'!$G$2:$H$6,2,FALSE),"").
Can the drop-down cell itself show the number instead of the text?
The data validation cell displays the selected list value. Use a separate cell, such as C2, to return the associated number with the lookup formula.
What happens if the mapping table contains duplicate statuses?
VLOOKUP returns the rate from the first matching row. Keep each status unique in the first column of the mapping table.




