Lookup
Cascading Dropdown Values
Create cascading (dependent) dropdown lists where the options in the second dropdown change based on what was selected in the first dropdown. Common for Category → Subcategory selections.
Excel Formula
=INDIRECT(A2)Step-by-Step Explanation
1
INDIRECT converts a text string into a cell reference
2
A2 contains the name of a Named Range
3
When the user selects a category, INDIRECT points to the right list
4
Each category's options are stored as a Named Range
5
Data Validation uses this formula for the dependent dropdown
Example
| Category (A) | Subcategory (B) |
|---|---|
| Fruit | Apple, Banana, Cherry |
| Vegetable | Carrot, Peas, Corn |
| Meat | Chicken, Beef, Pork |
Result: Data Validation source for B: =INDIRECT(A2) — options change based on category
Common Variations
With OFFSET
=OFFSET(INDIRECT(A2),0,0,COUNTA(INDIRECT(A2&"List")),1)Dynamic range size
FILTER approach (Sheets)
=FILTER(C:C,B:B=A2)Google Sheets: filter-based approach
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →