Function Problems

How to Create a Two-Cell Excel Drop-Down List with Automatic Values

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to create a linked system where picking a text choice from a drop-down list in one cell automatically displays a predefined numerical value (like 100%) in an adjacent second cell.

Product: Excel
Device & OS: not provided
Scenario: Setting up an automated data entry form or status tracking sheet where textual selections map to specific numerical metrics.
Observed behavior: Requires a dynamic solution to link data validation options to specific output values without manual data entry.

Before you start

Create a reference table in your worksheet (or on a separate tab) with two columns: place your text choices in the first column and their corresponding numerical values in the second column.

Solution 1Recommended

Use Data Validation combined with XLOOKUP

This is the most modern and robust method, using Excel's Data Validation feature to create the list and the XLOOKUP function to fetch the corresponding value.

XLOOKUP is available in newer versions of Excel (Microsoft 365 and Excel 2021). It allows you to search a range for a specific value and return an item from another column, making it perfect for dynamic drop-downs.

Set up the Data Validation list

Select the cell where you want the drop-down list to appear. Go to the 'Data' tab on the ribbon and click 'Data Validation'. In the 'Allow' drop-down, select 'List'. For the 'Source', highlight the first column of your reference table containing the text choices, then click 'OK'.

Input the XLOOKUP formula

Select the second cell where you want the automatic value to appear. Type the formula: =XLOOKUP(A1, ReferenceTextRange, ReferenceValueRange), replacing 'A1' with your drop-down cell, and selecting the corresponding columns from your reference table for the lookup and return ranges.

Test the automation

Click the drop-down arrow in the first cell and select an option like 'Fully Funded'. The second cell will immediately update to display the corresponding value, such as '100%'.

Formatting Output: If your returned value is a percentage, remember to apply Percentage formatting to the second cell via the Home tab to display it correctly (e.g., 100% instead of 1).

Solution 2

Use Data Validation combined with VLOOKUP

For users on older versions of Excel (Excel 2019 and earlier) that do not support XLOOKUP, VLOOKUP provides a reliable alternative.

WPS Spreadsheet Solution

Automate Cell Values with Drop-Downs in WPS Spreadsheet

WPS Spreadsheet provides powerful Data Validation and built-in lookup functions to automate your data entry effortlessly. You can seamlessly replicate the two-cell drop-down logic using a familiar interface.

1. Create the List: Open your workbook in WPS Spreadsheet, select the target cell, navigate to the Data tab, and click 'Validation' to create your drop-down list.
2. Add the Formula: In the adjacent cell, type your XLOOKUP or VLOOKUP formula referencing your data table.
3. Apply and Test: Press Enter. Selecting a new text option from the drop-down will now automatically update the value.

Fully compatible with Microsoft Excel formats (.xlsx) and formulas.Intuitive Data Validation menus for quick drop-down list creation.Built-in support for dynamic functions including VLOOKUP and XLOOKUP.Lightweight software that processes complex formulas quickly.

microsoft office alternative - wps office

Frequently Asked Questions

How do I hide the reference data used for my drop-down list?

You can place your reference table on a separate worksheet. Once your drop-down and lookup formulas are set up, right-click the worksheet tab containing the reference data and select 'Hide'. The drop-down will continue to work normally.

Why does my lookup formula show an #N/A error?

The #N/A error typically appears when the drop-down cell is empty or if the selected text doesn't exactly match the text in your reference table (e.g., trailing spaces). Wrapping your formula with IFERROR, like =IFERROR(XLOOKUP(...), ""), will display a blank cell instead of the error.

Can I automatically update my drop-down list when I add new options?

Yes. Format your reference table as an official Table by selecting it and pressing Ctrl+T. When you add new rows to this Table, your Data Validation list and lookup ranges will expand automatically without needing formula adjustments.

Excel & Data Problems