Function Problems

Create an Excel Drop-Down List That Displays Related Data

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Create a drop-down list that displays all related content for the selected item.

Create an Excel Drop-Down List That Displays Related Data
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to select a state, such as Alaska, and display its associated training topics.
Observed behavior
Selecting an item should display the corresponding list of topics in worksheet cells.
Before you start

Place each item's related topics in a separate, contiguous column and prepare a list of item names for the drop-down. Use names that are valid Excel range names, such as Alaska, and leave enough empty cells for the results.

Solution 1Recommended

Display a Named Range with a Drop-Down List in Excel 365

Use a named range for each item and a dynamic-array formula to display its related topics automatically.

1
Name each topic range

Select the cells containing Alaska's topics, enter Alaska in the Name Box to the left of the formula bar, and press Enter. Repeat for each item, using a matching valid range name.

2
Create the selection list

Enter the range names, such as Alaska, in worksheet cells. Select A1, open Data > Data Validation, choose List, and select those cells as the Source.

3
Display the related topics

Select D2 and enter =INDIRECT(A1). Choose Alaska in the A1 drop-down; the Alaska range should spill down from D2.

4
Check the output area

If Excel displays #SPILL!, clear any occupied cells below D2. If it displays #REF!, check that the selected text exactly matches an existing named range.

Display a Named Range with a Drop-Down List in Excel 365
Use valid range names: Named ranges cannot contain spaces or look like cell references. Use names such as New_York in both the drop-down list and the named range.
Work with related lists in WPS

Display Drop-Down Selections in WPS Spreadsheets

Use named ranges, data validation, and an INDEX formula in WPS Spreadsheets to display topics associated with a selected item.

  1. 1. Prepare the topic ranges: Open your workbook in WPS Spreadsheets. Select each column of related topics and use the Name Box beside the formula bar to assign a name such as Alaska.
  2. 2. Create the drop-down: Enter the matching range names in worksheet cells. Select A1 and use Data > Data Validation to create a List from those cells.
  3. 3. Enter the result formula: In D2, enter =IFERROR(INDEX(INDIRECT($A$1),ROWS($D$2:D2)),"").
  4. 4. Fill and test: Drag the fill handle down enough rows for the longest list, then select Alaska in A1 to display its topics.
Display related topics for each drop-down selectionOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet menus and formulasWork with a free, easy-to-use office suite
QA img-9

Frequently Asked Questions

Why does my formula show #REF! after I select an item?

The selected text may not match a named range. Check the names under Formulas > Name Manager and make sure the drop-down entry matches the intended name.

Can I use state names with spaces in the drop-down?

Named ranges cannot contain spaces. Use a valid name such as New_York for both the drop-down entry and its corresponding named range.

Why does Excel show #SPILL! instead of the topics?

The dynamic-array formula needs empty cells for its results. Clear any content blocking the spill area below the formula and try again.

Will the list update when I choose a different item?

Yes. Selecting another valid range name in A1 causes the INDIRECT formula to refer to that item's range. In the copied-formula method, make sure enough output rows are filled.