Lookup & Reference

Excel formula for Filter rows by criteria with a formula

Return all rows that meet a condition as a spilled list with FILTER.

Copy this formula

=FILTER(A2:C100, B2:B100>100)

How the formula works

FILTER returns every row of a range where the condition array is TRUE. Combine conditions with * (AND) or + (OR), and add a third argument for what to show when nothing matches.

Examples

=FILTER(A2:C100, (B2:B100>100)*(C2:C100="East"), "None")→spilled rows

Rows over 100 in the East region.

FAQ

I get a #SPILL! error — why?

Cells below or to the right of the formula aren't empty. Clear the spill range so the results have room.

Related formulas

Check if a value exists in a list Get a list of values without duplicates

Functions used

FILTER SORT UNIQUE

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free