🌊 Dynamic Filtering

Goal: Get a list of all products in a specific category.
FILTER returns multiple rows at once (Spill Array).

Result (Spill Range):
Ready...

💻 Formula Structure

=FILTER(array, include, [if_empty])
  • 1. Array: The range you want to filter (Source Data).
  • 2. Include: The condition (e.g., Range = "Fruit").
  • 3. If Empty: Text to show if no matches found.
-- Filter list for a category
=FILTER( A2:B6, B2:B6="Fruit", "None" )
Items Found:
-

⚠️ Common Pitfalls

🚫 #SPILL! Error
If there are any values blocking the result area, Excel returns #SPILL!. The path must be clear.
✅ Fix: Clear cells below the formula.
🚫 #CALC! Error
Occurs if no matches are found and you didn't provide the 3rd argument ([if_empty]).
✅ Fix: Always add ", "No Data"" at the end.