Excel
All Top 50 Excel & Business Modeling Questions#29
Building Cascading Dependent Dropdown Lists with Data Validation
MediumDeloitteInterview Question #29
Asked at DeloitteYou are designing an input template. In cell `A2`, a user selects a Country ('USA', 'Canada'). In cell `B2`, you want a dependent dropdown that displays only the States/Provinces for that chosen country. Explain how to configure this using Named Ranges and Data Validation with INDIRECT.
Input Table: ReferenceData
3 rows preview| USA (Named Range 'USA') | Canada (Named Range 'Canada') |
|---|---|
| California | Ontario |
| New York | Quebec |
| Texas | British Columbia |
Expected Output Structure2 rows
| Selected Country (A2) | Dropdown Options in B2 |
|---|---|
| USA | List: California, New York, Texas |
| Canada | List: Ontario, Quebec, British Columbia |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | USA (Named Range 'USA') | Canada (Named Range 'Canada') | ||||
| 2 | California | Ontario | Target [Enter Formula] | |||
| 3 | New York | Quebec | ||||
| 4 | Texas | British Columbia | ||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect