Function Problems

How to Create an Excel Drop-Down List That Returns a Named Range

Huda Qurayshi Sep 28, 2026 869 views

Question details

The user needs to create a drop-down list with user-friendly text options that dynamically returns data from a specific 7x5 named range table based on the selection.

How to Create an Excel Drop-Down List That Returns a Named Range

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic drop-down menu where selecting a readable option pulls data from a specific named range that has a different, system-compliant name.
Observed behavior: The drop-down entries contain spaces and cannot be used directly as named ranges, requiring a lookup method to map the user selection to the actual named range.

Before you start

Ensure you have already defined your named ranges for the 7x5 tables in the Name Manager (without spaces), and determine the exact cell where your drop-down list will be located.

Solution 1Recommended

Use a Mapping Table with XLOOKUP and INDIRECT

The most robust way to link user-friendly drop-down names to named ranges is by creating a background mapping table and pulling the data dynamically.

Excel named ranges cannot contain spaces, so user-friendly drop-down options must be mapped to their corresponding named ranges. By using a helper table, XLOOKUP, and the INDIRECT function, you can dynamically retrieve the required table without errors.

Create a mapping table

Set up a two-column table in your workbook. Place your user-friendly choices (with spaces) in the first column, and the exact named-range names (without spaces) in the second column.

Configure Data Validation

Select your target cell for the drop-down. Go to Data > Data Validation, choose 'List', and highlight the first column of your mapping table as the Source.

Retrieve the named range

In the cell where you want the 7x5 table to appear, enter the formula =IFERROR(INDIRECT(XLOOKUP(A2, A11:A12, B11:B12)), ""). Assume A2 is your drop-down cell, A11:A12 is the display text column, and B11:B12 is the named-range column.

Use a Mapping Table with XLOOKUP and INDIRECT

Formula Explanation: XLOOKUP finds the corresponding named range text based on the drop-down selection, and INDIRECT converts that text string into an actual range reference that Excel displays.

Solution 2

Use a Mapping Table with VLOOKUP (For Older Excel Versions)

If you are using an older version of Excel that does not support the XLOOKUP function, VLOOKUP can achieve the exact same result.

Easily Create Dynamic Drop-Downs with WPS Spreadsheet

WPS Office fully supports advanced data validation and lookup formulas, allowing you to create complex mapping tables and dynamic drop-down lists effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to create the drop-down list.
2. Set up Data Validation: Highlight your target cell, navigate to the 'Data' tab on the ribbon, select 'Validation', and choose your user-friendly names as a 'List'.
3. Apply the lookup formula: Use the =INDIRECT(XLOOKUP(...)) or VLOOKUP formula exactly as you would in Microsoft Excel to dynamically display your targeted named range.

100% compatible with Microsoft Excel named ranges and array formulasFully supports advanced functions like INDIRECT, XLOOKUP, and VLOOKUPIntuitive Data Validation tool for quick drop-down menu creationFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use spaces in my Excel named ranges?

Excel requires named ranges to begin with a letter or underscore and cannot contain spaces or most special characters. Because of this restriction, a mapping table is required to bridge the gap between user-friendly display names and system-compliant named ranges.

What exactly does the INDIRECT function do in this formula?

The INDIRECT function takes a text string (such as the name of your range that XLOOKUP returned) and converts it into a valid cell or range reference that Excel can actually evaluate, allowing the 7x5 table data to populate.

Can I permanently copy the values of the named range instead of referencing them dynamically?

Formulas like INDIRECT only reference and display the data dynamically. If you need to physically copy and paste the 7x5 table's values into destination cells based on a drop-down selection, you would need to use a VBA macro.

Why is my drop-down formula returning a #REF! error?

A #REF! error usually occurs if the text string returned by your lookup formula does not perfectly match the actual Name defined in the Name Manager, or if the named range has been deleted or misspelled in the mapping table.

Excel & Data Problems