FILTER returns all rows meeting a condition, which is useful when one key legitimately has several records. It does not edit or remove rows from the source dataset.
Define the output and condition
For records in A2:C100 with status in C, =FILTER(A2:C100,C2:C100="Open") returns the Open rows. Put the formula in a clear output area with enough columns and rows.
Keep the condition range aligned with the data range. The first condition value must describe the first data row, and the condition must have the same length as the dimension being filtered.
Add conditions with clear logic
Additional condition arguments require each included row to pass all those conditions. For example, a status condition and a region condition together define their intersection. Do not assume separate arguments mean either condition may pass.
FILTER cannot mix row and column conditions in one call. If both dimensions need filtering, design the steps deliberately rather than supplying incompatible condition arrays.
Handle no matches and growth
When nothing qualifies, FILTER returns #N/A. IFNA can provide a clear No matching records label when that is an expected state. Keep unexpected reference or shape errors visible during testing.
Check a dataset with zero, one, and several matching rows. Compare IDs and counts with the source. Leave the result area free of manual entries that would block expansion. For growing data, review both the output capacity and the source range boundary; the formula cannot include new records outside the range merely because its current results expand automatically.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.