Create an Excel Drop-Down List That Displays Related Data
Question details
Create a drop-down list that displays all related content for the selected item.

- 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.
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.
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.
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.
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.
Select D2 and enter =INDIRECT(A1). Choose Alaska in the A1 drop-down; the Alaska range should spill down from D2.
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 Related Topics in Older Excel Versions
Use INDEX with INDIRECT and copy the formula down when dynamic-array spilling is unavailable.
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. 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. 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. Enter the result formula: In D2, enter =IFERROR(INDEX(INDIRECT($A$1),ROWS($D$2:D2)),"").
- 4. Fill and test: Drag the fill handle down enough rows for the longest list, then select Alaska in A1 to display its topics.

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.




