Function Problems

How to Create a Dependent Dropdown for State and County Data in Excel

Steve K Sep 28, 2026 869 views

Question details

The user needs to set up a dependent dropdown list where the county selection is filtered by the chosen state, and correctly configure INDEX, MATCH, and INDIRECT formulas to retrieve matching data from other sheets.

How to Create a Dependent Dropdown for State and County Data in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Building a master data sheet that requires cascading dropdown menus (State, then County) and pulling in related information from other worksheets based on the selection.
Observed behavior: The dependent dropdown setup and the associated INDEX, MATCH, and INDIRECT lookup formulas are currently returning #N/A errors instead of the correct data.

Before you start

Ensure your source data for states and counties is organized in clean columns without blank cells, and verify that there are no spaces in the names you plan to use for named ranges.

Solution 1Recommended

Set Up Dependent Dropdowns using Named Ranges

Use Excel's Data Validation feature combined with Named Ranges and the INDIRECT function to link the second dropdown directly to the first dropdown's selection.

This method relies on creating specific 'Named Ranges' for each state's counties. When a user selects a state, the INDIRECT function reads that text and points Data Validation to the corresponding named range.

Create Named Ranges for Counties

Select the list of counties for a specific state on your source sheet. Go to the Formulas tab, click 'Define Name', and enter the exact state name (e.g., 'Texas'). Repeat this for every state.

Set up the State Dropdown

On your Master sheet, select the cell intended for the State dropdown (e.g., A2). Go to Data > Data Validation, choose 'List' under Allow, and select your master list of states as the Source.

Set up the County Dependent Dropdown

Select the cell for the County dropdown (e.g., B2). Go to Data > Data Validation, choose 'List', and in the Source field, enter the formula: =INDIRECT(A2). Click OK.

Set Up Dependent Dropdowns using Named Ranges

Handling Multi-word Names: Named ranges cannot contain spaces (e.g., 'New York' must be named 'New_York'). To make the dropdown work with spaces, use the SUBSTITUTE function inside INDIRECT: =INDIRECT(SUBSTITUTE(A2, " ", "_")).

Solution 2

Troubleshoot #N/A Errors in INDEX and MATCH

Resolve #N/A errors by ensuring exact text matches and correcting the range alignments in your INDEX and MATCH formulas.

Create Cascading Dropdowns in WPS Spreadsheet

Easily Build Dependent Dropdowns and Lookups with WPS Office

WPS Spreadsheet provides robust support for Data Validation, Name Manager, and complex lookup functions like INDEX, MATCH, and INDIRECT. You can seamlessly build and manage dependent state-county dropdowns without errors.

1. Open and Define Names: Open your workbook in WPS Spreadsheet, highlight a state's county data, and press Ctrl + F3 to create a Named Range for that state.
2. Apply Data Validation: Select the target cell, navigate to the Data tab, click 'Validation', select 'List', and input your state list reference.
3. Use INDIRECT for Dependency: In the county cell, apply Data Validation as a List and use the formula =INDIRECT($A$2) (referencing your state cell) to link the dropdowns.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Intuitive Data Validation menu for quick dropdown creation.Built-in Name Manager for easy organization of state and county named ranges.Free, lightweight, and fast alternative to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula in Data Validation returning an error prompting me to continue?

This happens when the parent dropdown cell (e.g., the State cell) is currently blank, making the INDIRECT function evaluate to an error temporarily. Simply click 'Yes' to continue; the dropdown will work perfectly once a state is selected.

How can I create a dependent dropdown without using Named Ranges?

If you are using newer spreadsheet software that supports dynamic arrays, you can use the FILTER function inside Data Validation, or utilize a combination of OFFSET and MATCH. However, Named Ranges paired with INDIRECT remains the most stable and backward-compatible method.

Why does my INDEX and MATCH combination return #N/A even when the text looks identical?

The most common culprits are mismatched data types (e.g., a number stored as text) or hidden trailing spaces. Use the TRIM function on both your lookup value and array, or use the CLEAN function to remove non-printable characters.

Can I add a third dropdown that is dependent on the second one?

Yes, you can create a third level (e.g., State > County > City) by applying the exact same logic. Create Named Ranges for each county containing its respective cities, and use the INDIRECT formula referencing the county dropdown cell for the city's Data Validation list.

Excel & Data Problems