Filter Rows That Match a Condition

Excel & Google Sheets

How it works

FILTER returns every row of A2:C10 where the matching row of C2:C10 is over 1,000, spilling the results into the cells below and to the right — one formula, a whole live table that recalculates the moment the source data changes. The third argument is Excel's answer for the empty case: if no row passes, the cell shows "No matches" instead of an error. Google Sheets has FILTER too and the first two arguments work identically, but Sheets has no if-empty argument — it treats a third argument as another condition, and when nothing matches it returns #N/A instead. In Sheets, wrap the formula in IFNA to get the same friendly message: =IFNA(FILTER(A2:C10,C2:C10>1000),"No matches"). In Excel, FILTER needs 365 or 2021; older versions show #NAME?.

A2:C10

The rows to return — all three columns come back for every matching row.

C2:C10>1000

The condition, one TRUE/FALSE per row. It must span exactly the same rows as the source range.

"No matches"

Excel only: what to show when nothing passes. Sheets doesn't accept this argument — use IFNA there instead.

When to use it

Use it for live extracts: open deals above a threshold, unpaid invoices, one customer's orders, every project task assigned to one owner — anywhere you'd otherwise re-filter and re-paste each week.

Common mistakes

• ✕Condition and source ranges cover different rows.

  ✓=FILTER(A2:C10,C2:C9>1000) returns #VALUE! — the condition spans 8 rows, the source 9. Make both ranges start and end on the same rows.

• ✕Something is sitting in the spill zone.

  ✓FILTER needs empty cells below and to the right for its results; even a stray space causes #SPILL!. Clear the landing area.

• ✕Passing "No matches" as the third argument in Google Sheets.

  ✓Sheets reads it as a second condition and errors. Use =IFNA(FILTER(A2:C10,C2:C10>1000),"No matches") instead.

• ✕#NAME? in Excel 2019 or earlier.

  ✓FILTER needs Excel 365 or 2021. On older versions there's no direct equivalent — use a helper column plus sorting, or upgrade.