Array Formulasbeginner
Extract a unique list with UNIQUE
=UNIQUE(A2:A100)
Excel how-to guide
How to filter rows by condition in Excel
Extract all orders for a specific region, all tasks assigned to one person, or all products above a price threshold into a separate area automatically.
Step-by-step instructions
How to filter rows by condition — 3 steps
1
Enter the full data range (all columns you want to return) as the first argument.
2
Enter the condition array as the second argument — this must be the same height as the data range.
3
Enter a fallback value as the third argument to display when no rows match instead of an error.
Example data
Worked example
| Order | Region | Amount |
|---|---|---|
| ORD-001 | East | 1200 |
| ORD-002 | West | 800 |
| ORD-003 | East | 450 |
| ORD-004 | North | 2100 |
Common mistakes
Errors to watch out for
FILTER is not available
FILTER requires Excel 365 or Excel 2021. Use Advanced Filter or a VLOOKUP workaround for older versions.
SPILL error
Clear the cells below and to the right of the formula — FILTER needs empty space to spill its results.
Multiple conditions
Use * for AND logic — (B2:B100="East")*(C2:C100="Active") — and + for OR logic — (B2:B100="East")+(B2:B100="West").
FAQ
Frequently asked questions
How do I filter by multiple conditions?
Multiply the condition arrays for AND logic — =FILTER(A2:C100,(B2:B100="East")*(D2:D100>1000),"No results") returns rows where region is East AND amount exceeds 1000.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Array Formulasbeginner
Sort a range dynamically with SORT
=SORT(A2:B20,2,-1)