FILTER Function Generator
Return every row of a range that meets your condition, as a spilled dynamic array.
FILTER
Return every row of a range that meets your condition, as a spilled dynamic array.
The rows and columns you want returned. Do not include the header row.
A test that produces TRUE/FALSE for each row, with the same number of rows as the array.
What to show when nothing matches. Excel only - Google Sheets FILTER has no if_empty argument, so for Sheets delete this third argument from the copied formula and wrap the result in IFERROR.
=FILTER($A$2:$C$100, $C$2:$C$100>1000, "No results")Worked Example
An orders table sits in A2:C100 with the order amount in column C, and you want every order over 1,000.
=FILTER($A$2:$C$100, $C$2:$C$100>1000, "No results")Returns: Spills all three columns of every row whose column C value exceeds 1000 into the cells below and right of the formula, or shows "No results" if no order qualifies.
Checks Before You Paste
- •#SPILL! means the results have nowhere to land. Clear the cells below and to the right of the formula, and note that dynamic arrays cannot spill inside an Excel Table - move the formula outside the Table.
- •The include test must have exactly as many rows as the array (or as many columns, for a horizontal filter). $A$2:$C$100 with a test over $C$2:$C$50 returns #VALUE!, and full-column references on one side only are the usual cause.
- •Combine conditions with arithmetic, not AND/OR: multiply for AND - ($C$2:$C$100>1000)*($B$2:$B$100="West") - and add for OR. AND() and OR() collapse the whole array to a single TRUE/FALSE and quietly return everything or nothing.