Function Problems

How to Return the Correct Numeric Value from an Excel Selection

Natalie TaylorNatalie Taylor Nov 10, 2026 868 views

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.

How to Return the Correct Numeric Value from an Excel Selection
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').
Before you start

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.

Solution 1Recommended

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.

1
Locate the linked cell

Right-click your form control, select 'Format Control', and note the 'Cell link' (e.g., A1). This cell contains the index number.

2
Identify the source range

Find the range of cells containing the actual values populated in the dropdown (e.g., D1:D10).

3
Enter the INDEX formula

Select a blank cell where you want the actual value to appear and type =INDEX(D1:D10, A1). Press Enter.

4
Test the selection

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 the INDEX Function to Retrieve the Actual Value
Dynamic Updates: Whenever you update the values in your source range, the INDEX formula will automatically reflect the new corresponding values.
Efficient Spreadsheet Management

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. 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your dropdown list or form control.
  2. 2. Navigate to Formulas: Click the 'Formulas' tab to access the comprehensive library of functions, including Lookup & Reference functions like INDEX.
  3. 3. Apply the INDEX function: Type =INDEX(source_range, linked_cell) to instantly retrieve the actual numeric value from your selection.
  4. 4. Format Data Validation easily: Alternatively, use the 'Data' tab > 'Validation' feature to create direct-input dropdown lists that require no formula conversion.
Fully compatible with Microsoft Excel formulas, form controls, and dynamic arraysIntuitive interface for setting up Data Validation lists in secondsLightweight application that runs smoothly without system lagFree access to powerful data analysis and extraction tools
microsoft office alternative - wps office

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.