How to Return the Correct Numeric Value from an Excel Selection
Question details
The user needs to retrieve the actual numerical value of a selected item in an Excel control rather than the list index position.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using an Excel form control or dropdown list where selecting an item returns its position number instead of the underlying value.
- Observed behavior
- Selecting an item from the control returns its sequential index (e.g., selecting the fifth item returns '5') even if the item represents a different numeric value (e.g., '8').
Identify the exact cell range containing your original list of values and locate the linked cell where your form control currently outputs the index position.
Use the INDEX Function to Retrieve the Actual Value
Apply the INDEX function to look up the correct value from your source list based on the index number returned by the form control.
Form controls, such as Combo Boxes and List Boxes, inherently return the position index of the selected item. To convert this index back into meaningful data, you must reference the original list.
Right-click your form control, select 'Format Control', and note the 'Cell link' (e.g., A1). This cell contains the index number.
Find the range of cells containing the actual values populated in the dropdown (e.g., D1:D10).
Select a blank cell where you want the actual value to appear and type =INDEX(D1:D10, A1). Press Enter.
Change the selection in your form control. The cell with the INDEX formula will now update to display the correct numeric value instead of the position index.

Use Data Validation Instead of Form Controls
Replace the form control with an in-cell Data Validation dropdown to directly output the selected value into the cell without needing an index conversion.
Generate a Limited Array Sequence (Advanced)
If your goal is to generate a dynamic sequence of values limited by a specific total count, use dynamic array functions like WRAPROWS and SEQUENCE.
Easily Manage Data and Formulas with WPS Spreadsheet
WPS Spreadsheet provides seamless execution of advanced lookup formulas and data validation rules. Easily extract exact values from form controls using built-in functions like INDEX and SEQUENCE without complicated workarounds.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your dropdown list or form control.
- 2. Navigate to Formulas: Click the 'Formulas' tab to access the comprehensive library of functions, including Lookup & Reference functions like INDEX.
- 3. Apply the INDEX function: Type =INDEX(source_range, linked_cell) to instantly retrieve the actual numeric value from your selection.
- 4. Format Data Validation easily: Alternatively, use the 'Data' tab > 'Validation' feature to create direct-input dropdown lists that require no formula conversion.

Frequently Asked Questions
Why does my Excel combo box return a number instead of the text or value?
ActiveX and Form controls, like combo boxes and list boxes, are designed to output the relative index position (e.g., 1 for the first item, 2 for the second) to the linked cell. This is expected behavior. You must use an INDEX formula to fetch the actual underlying value.
How do I change the linked cell for a form control?
Right-click the form control, select 'Format Control', and go to the 'Control' tab. Find the 'Cell link' box and select or type the reference of the new cell where you want the index number to appear.
Does WPS Spreadsheet support dynamic array functions like SEQUENCE and WRAPROWS?
Yes, modern versions of WPS Spreadsheet fully support dynamic array functions. You can utilize formulas like SEQUENCE, WRAPROWS, and LET directly, ensuring compatibility with files originally created in Microsoft Excel.




