Function Problems

Excel Formula to Return a Value from a Drop-Down Selection

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula that automatically returns a specific corresponding result when one of 15 different values is selected from a drop-down list.

Product: Excel
Device & OS: not provided
Scenario: Setting up a spreadsheet where a cell's output dynamically changes based on a user's selection from a Data Validation drop-down menu.
Observed behavior: Requires mapping drop-down options (e.g., 1, 2, 3) to specific output values (e.g., 30, 28, 26) without causing formula errors.

Before you start

Ensure your drop-down list is already created using the Data Validation feature, and locate a blank area in your workbook to build a reference table.

Solution 1Recommended

Use a Lookup Table with VLOOKUP or XLOOKUP

This is the most reliable method for mapping multiple distinct values (like 15 drop-down options) to their corresponding results.

Instead of writing a complex nested IF formula, creating a separate two-column lookup table is highly recommended. It keeps your formulas clean and makes it easy to update values later.

Create a reference table

In an empty area of your sheet (e.g., G2:H16), list your 15 drop-down options in the first column and the corresponding return values in the second column.

Select the target cell

Click the cell where you want the corresponding value to appear.

Enter the VLOOKUP formula

Type =VLOOKUP(D2, G2:H16, 2, FALSE) assuming D2 is the cell with your drop-down list. Press Enter.

Alternative: Use XLOOKUP

If you are using a newer version of Excel or WPS Spreadsheet, type =XLOOKUP(D2, G2:G16, H2:H16) for a more flexible lookup.

Lock Your References: If you plan to drag and copy this formula to other cells, remember to lock your table range using absolute references, like $G$2:$H$16.

Solution 2

Apply the LOOKUP Function with Named Ranges

A clean alternative that utilizes named ranges instead of direct cell references, making your formulas easier to read and troubleshoot.

Solution 3

Use a Mathematical Formula (For Sequential Data Only)

A quick calculation method that bypasses the need for a lookup table, but it strictly requires your drop-down values to be sequentially numbered.

Spreadsheet Mastery

Effortlessly Manage Drop-Downs and Lookups with WPS Office

WPS Spreadsheet provides complete support for advanced data validation and lookup functions, including VLOOKUP and XLOOKUP, allowing you to easily map drop-down selections to corresponding values.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your drop-down list.
2. Create a reference table: Designate a two-column area in your worksheet to map the drop-down options to their desired return values.
3. Insert the lookup formula: Click your target cell, type =XLOOKUP or =VLOOKUP, and select your drop-down cell as the lookup value.
4. Highlight the table arrays: Select the reference table arrays to complete the formula, press Enter, and instantly see the mapped result.

Fully compatible with Microsoft Excel (.xlsx) formulas and formattingIncludes built-in support for advanced functions like XLOOKUP and VLOOKUPIntuitive Data Validation tools for easily managing drop-down listsLightweight, fast, and free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my LOOKUP formula return an #N/A error?

The #N/A error usually occurs if the drop-down value selected does not exist in the first column of your lookup table, or if you dragged the formula down without using absolute cell references (like $A$1:$B$15) to lock the table range.

Can I use an IF or IFS function instead of a lookup table?

Yes, you can use the nested IF or the IFS function (e.g., =IFS(D2=1, 30, D2=2, 28...)). However, for 15 different options, writing an IFS formula becomes exceptionally long and prone to typos. A lookup table is much cleaner and easier to update.

Does WPS Spreadsheet support the XLOOKUP function?

Yes, modern versions of WPS Office fully support the XLOOKUP function. It provides a more robust and flexible way to search for a value in an array and return a corresponding item compared to older VLOOKUP or HLOOKUP functions.

Excel & Data Problems