A list that depends on the first choice
PremiumThe second drop-down should only offer items in the category picked in E1. In E2, list the items in B2:B8 whose category in A2:A8 matches E1, with FILTER.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Dairy | ||
| 2 | Fruit | Apple | Items | |||
| 3 | Fruit | Banana | ||||
| 4 | Dairy | Milk | ||||
| 5 | Fruit | Pear | ||||
| 6 | Dairy | Cheese | ||||
| 7 | Bakery | Bread | ||||
| 8 | Dairy | Butter | ||||
| 9 |
Premium lesson
Unlock Drop-down lists and data validation and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:COUNTIFSUMIF
Need help?
Why this works
FILTER returns the items in B2:B8 where the category in A2:A8 equals E1. With Dairy in E1, that is Milk, Cheese and Butter.
Point the second drop-down's Source at the spill with =$E$2#. Pick Fruit in E1, and the second drop-down offers Apple, Banana and Pear instead.
Common mistake: Typing the category into the formula. The list then stays the same whatever is picked in the first drop-down.
The answer
- E2
=FILTER(B2:B8,A2:A8=E1)