Function Problems

How to Return a Named Range or Table from an Excel Dropdown List

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants an Excel dropdown list where selecting a user-friendly label automatically retrieves and displays an entire defined table (e.g., a 7-by-5 cell grid).

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic dashboard or report where a dropdown selection swaps out the displayed data table.
Observed behavior: When selecting an option, the dropdown returns the actual text of the named-range name (especially when friendly labels contain spaces) rather than displaying the contents of the range itself.

Before you start

Ensure your target data tables are already created and assigned valid Named Ranges (which cannot contain spaces) in your Excel workbook Name Manager.

Solution 1Recommended

Use a Mapping Table with INDIRECT and VLOOKUP

This solution maps user-friendly dropdown names (which may contain spaces) to strict Named Ranges, and uses the INDIRECT function to extract the actual table data.

Excel Named Ranges do not allow spaces, which forces users to create rigid, unfriendly names. To keep your dropdowns clean and readable, you can create a background mapping table. The VLOOKUP function finds the strict Named Range based on the friendly dropdown selection, and the INDIRECT function translates that text name into the actual cell grid.

Create a Mapping Table

In a hidden sheet or unused area (e.g., A11:B12), list your user-friendly dropdown labels in column A (e.g., 'Region 1 Sales'). In column B, enter the exact Named Range string assigned to that table (e.g., 'Region1_Sales').

Set Up Data Validation

Click the cell where you want your dropdown to appear (e.g., A2). Go to Data > Data Validation, choose 'List', and highlight the friendly names in your mapping table (A11:A12) as the Source.

Apply the Data Retrieval Formula

In the top-left cell where the 7-by-5 table should appear, enter a formula to retrieve the data. Use: =IF(INDIRECT(VLOOKUP($A$2,$A$11:$B$12,2,FALSE))=0,"",INDIRECT(VLOOKUP($A$2,$A$11:$B$12,2,FALSE))). This looks up the strict name and evaluates it as a range.

Allow the Array to Spill

Press Enter. If you are using a modern spreadsheet version supporting dynamic arrays, the 7-by-5 table will automatically spill into the adjacent cells. Ensure the surrounding 7-by-5 area is completely empty to avoid a #SPILL! error.

Handling Blank Cells in Tables: The IF statement in the formula prevents Excel from displaying a '0' if a cell within your target Named Range happens to be completely empty.

Advanced Spreadsheet Data Validation

Create Dynamic Dropdowns and Retrieve Tables with WPS Spreadsheet

WPS Spreadsheet fully supports Data Validation, Name Manager, and dynamic array formulas like INDIRECT and VLOOKUP. You can easily build dynamic dashboards and interactive reports that retrieve complete tables without compatibility issues.

1. Define Your Names: Open your workbook in WPS Spreadsheet and define your table ranges via the Formulas tab > Name Manager.
2. Build the Mapping Table: Create a mapping area linking your desired readable dropdown text to the strictly formatted Defined Names.
3. Insert the Dropdown: Navigate to Data > Validation to set up your dropdown list using the friendly names as the source data.
4. Extract the Table: Use the =INDIRECT(VLOOKUP(...)) formula in your target cell to dynamically pull the selected table data into your current worksheet view.

Fully compatible with Microsoft Excel formulas and Named RangesSeamlessly handles Data Validation and complex VLOOKUP arraysFree, lightweight, and fast to load large datasetsIntuitive interface for managing Name Manager and lookup tables

microsoft office alternative - wps office

Frequently Asked Questions

Why is my dropdown formula showing the named range text instead of the table data?

This happens if you reference the text directly (e.g., using only VLOOKUP) without wrapping it in the INDIRECT function. INDIRECT is required to tell the spreadsheet to evaluate the text string as a cell reference or Named Range, which then displays the underlying data.

Can I use spaces in my Excel Named Ranges?

No, spreadsheet software does not allow spaces in Named Ranges. You must use underscores (e.g., Sales_Data) or CamelCase. This strict rule is why a mapping table is highly recommended to link friendly names containing spaces to the exact Named Range.

How do I make the formula display the entire 7x5 table?

In spreadsheet versions that support dynamic arrays, referencing a Named Range via INDIRECT will automatically 'spill' the data into the required rows and columns. Just type the formula in the top-left cell of where you want the table to appear and ensure nothing is blocking the 7x5 destination area.

What if my Named Range table contains blank cells?

By default, returning a blank cell via an array or INDIRECT formula might display a zero. You can prevent this by adding an IF statement to check for blanks, returning an empty string ("") instead of zero.

Excel & Data Problems