Function Problems

Map Excel Drop-Down Values to Numbers with a Lookup Formula

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Return a corresponding numeric rate when a user selects a text value from an Excel drop-down list.

Map Excel Drop-Down Values to Numbers with a Lookup Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the mapping table

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.

2
Check the drop-down

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.

3
Enter the lookup formula

Select C2 and enter =IFERROR(VLOOKUP(B2,$G$2:$H$6,2,FALSE),""). Select a status in B2 to display its rate in C2.

4
Fill the formula down

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.

Use VLOOKUP to Return the Rate for a Drop-Down Selection
Blank results: IFERROR returns a blank if the selection is not found or another formula error occurs. Check the status spelling and table range if an expected rate does not appear.
WPS Spreadsheets

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. 1. Open your worksheet: Open the spreadsheet in WPS Spreadsheets and identify the drop-down cell, such as B2.
  2. 2. Enter the mapping: Type the status choices in G2:G6 and their numeric rates in H2:H6.
  3. 3. Add the formula: Select C2 and enter =IFERROR(VLOOKUP(B2,$G$2:$H$6,2,FALSE),"").
  4. 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.
Return numeric rates from drop-down selections with VLOOKUPOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data validation tools
microsoft office alternative - wps office

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.