In Google Sheets, use FILTER to return rows or columns that meet conditions; it expands its results automatically, so wrapping it in ARRAYFORMULA is usually unnecessary. For example, with records in A2:C1000 and status values in column C, =FILTER(A2:C1000,C2:C1000="Open") returns the records marked Open.
What “array formula filter” means in Sheets
People searching for an “array formula filter” are often trying to make a list update automatically when their source data changes. In Google Sheets, FILTER is the function designed for that job: it checks a range against one or more conditions and returns the matching values as an array. The result can occupy several rows and columns, rather than only the cell containing the formula.
That behavior is useful for live views of a larger table. A task list can show only incomplete work, a product sheet can show one category, or a contact list can show people from a chosen region. When the source values change, the filtered result recalculates without copying the formula down each row or rerunning a manual filter.
The phrase “array formula” can also refer to ARRAYFORMULA, a separate Google Sheets function that applies an expression across a range. It is important to distinguish the two: FILTER already handles an array of input cells and returns an array result. In most ordinary filtering tasks, use FILTER by itself.
FILTER syntax and how its conditions work
The general syntax is =FILTER(range, condition1, [condition2, ...]). The first argument is the data to return. Each condition is a same-height or same-width range of TRUE/FALSE results that determines which corresponding rows or columns qualify. Square brackets in syntax descriptions mean an argument is optional; do not type those brackets into the formula.
- range: the cells to return, such as a table of names, categories, and statuses.
- condition: a comparison or other test, such as a status being equal to Open.
- additional conditions: optional tests combined with the first condition as an AND requirement.
For row filtering, the condition range must have the same number of rows as the data range. If the data range is A2:C1000, a condition such as C2:C1000 lines up row by row. A mismatch in range sizes produces an error because Sheets cannot pair each test with the correct record. You can use open-ended ranges, but bounded ranges are often easier to audit and can avoid processing many unused cells.
Build a basic filtered list
Suppose a worksheet has headers in row 1, names in column A, departments in B, and task statuses in C. To show only open tasks, enter the formula below in an empty cell outside the source table. This layout assumes the records occupy rows 2 through 1000.
=FILTER(A2:C1000,C2:C1000="Open")
Sheets returns the matching full rows, including each name, department, and status. If rows 3, 7, and 11 contain the exact status Open, those three records appear in the result in their original order. The formula does not move or delete the source rows; it creates a separate view. If a status changes from Open to Closed, that row disappears from this result after recalculation.
Place the formula where there is enough clear space for the output to expand downward and across. Keep the source headers out of the filtered range unless you deliberately want them tested as data. If you want the output to have its own header, type a header row above the formula or construct a separate header row.
Use AND, OR, text, and number criteria
To require multiple conditions at once, give each condition as a separate argument. For example, with names in A2:A1000, departments in B2:B1000, and statuses in C2:C1000, the following returns rows that are both in Sales and Open. The condition ranges line up with the records in A2:C1000.
=FILTER(A2:C1000,B2:B1000="Sales",C2:C1000="Open")
Separate conditions act like AND: a row must satisfy every condition. This pattern also works for numeric tests. With prices in column B and the full product table in A2:C1000, =FILTER(A2:C1000,B2:B1000>=50) returns products priced at 50 or more. If the values represent dates, comparisons such as greater than or equal to a date can filter a date range, provided the cells contain real date values rather than text that merely looks like a date.
For OR logic, combine tests into one condition. With regions in B2:B1000 and rows in A2:C1000, use =FILTER(A2:C1000,((B2:B1000="East")+(B2:B1000="West"))>0) to include either region. The plus sign adds the TRUE/FALSE tests as 1s and 0s; the greater-than-zero test turns the result back into a Boolean condition. Parentheses make the order of operations clear.
For a partial text match, use REGEXMATCH. With product names in A2:A1000 and full records in A2:C1000, =FILTER(A2:C1000,REGEXMATCH(A2:A1000,"(?i)tea")) returns rows whose names contain tea, ignoring letter case. The pattern is a regular expression, so special characters can have special meanings. For a simple exact match, use an ordinary equality test instead.
Filter selected data and control the output
The range argument controls which columns appear in the result. If your source table has extra fields that should not be shown, select only the block you want returned, as long as it remains aligned with the condition ranges. FILTER can return a single column as well as a multi-column table. It preserves the order of qualifying rows in the source; it does not automatically sort them.
To sort a filtered result, nest FILTER inside SORT. With data in A2:C1000 and status in C, this formula returns Open records sorted by the second column of the returned block, ascending:
=SORT(FILTER(A2:C1000,C2:C1000="Open"),2,TRUE)
The number 2 refers to the second column within the filtered output range, not necessarily column B on the worksheet if your returned range starts somewhere else. Use FALSE as the final SORT argument for descending order. Keep the nesting readable: first decide which rows qualify, then decide how to order the result.
When there are no matches, FILTER returns a #N/A error rather than an empty result. If a friendly message is preferable, and statuses are in C2:C1000, wrap the filter in IFNA: =IFNA(FILTER(A2:C1000,C2:C1000="Open"),"No matching rows"). This handles the no-match case while allowing other kinds of formula errors to remain visible.
When ARRAYFORMULA is useful—and when it is not
In Google Sheets, array behavior means a formula can work with ranges and produce multiple results. FILTER has that behavior built in. Adding ARRAYFORMULA around a normal FILTER expression generally adds no benefit, and can make an otherwise clear formula harder to read. You do not need to fill a FILTER formula down a column; enter it once and let the result expand.
ARRAYFORMULA is useful when you want a regular expression, such as joining two cells, applied across many rows. For example, if names are in A2:A1000 and departments are in B2:B1000, this formula creates a combined label for each nonblank name:
=ARRAYFORMULA(IF(A2:A1000="","",A2:A1000&" - "&B2:B1000))
That is a row-by-row calculation, not a filter. You could then use FILTER on the generated labels or use the underlying columns as filter conditions. Keeping the jobs separate is often easier to maintain: use ARRAYFORMULA to calculate a column of values and FILTER to select records.
Another common misconception is that a FILTER formula needs the old Ctrl+Shift+Enter array-entry method. In Google Sheets, enter it normally and press Enter. That shortcut is not required for FILTER to return multiple cells.
Common errors and practical fixes
- #N/A: This usually means no row met the conditions. Check spelling, spaces, dates, and whether the criteria are too restrictive. Use IFNA only if a no-match message is useful.
- Range-size mismatch: Ensure each condition range has the same number of rows as the rows in the returned range. For example, avoid pairing A2:C1000 with a condition range that starts at row 1 or ends at row 500.
- Array result was not expanded: One or more cells in the output area contain data, or a merged cell blocks the result. Clear the obstructing cells or move the formula to an empty area; do not erase source data to make room.
- Unexpected missing matches: Look for leading or trailing spaces, inconsistent labels, or values stored in a different type. A number stored as text may not behave like a numeric value in a comparison.
- Header appears or is missing: Check where the data range starts. A range starting at row 1 includes the header, while one starting at row 2 excludes it.
Equality comparisons in Sheets are generally not case-sensitive, so Open and OPEN normally compare as equal. If capitalization must matter, use EXACT as the test. With status values in C2:C1000 and records in A2:C1000, =FILTER(A2:C1000,EXACT(C2:C1000,"Open")) selects the exact case-sensitive text Open. As with any condition, the tested range must match the records row for row.
FILTER compared with other ways to find data
A filter view or the built-in filter controls are handy when a person wants to inspect a table interactively and temporarily hide rows. A FILTER formula is better when the goal is a separate, automatically updating output area that can feed another report or formula. The source data remains visible in either case, but a formula gives you a repeatable result on the sheet.
QUERY is another Google Sheets option. It can filter records and also supports query-style operations such as selecting columns, ordering, and grouping. For a simple condition, FILTER is often more approachable because its criteria resemble ordinary spreadsheet comparisons. For grouped summaries or a compact multi-operation query, QUERY may be a better fit. Neither function changes the underlying source records.
For a one-off check, Sheets’ Find feature or a manual filter may be quicker than writing a formula. For a reusable dashboard or a list that should respond to changing criteria, FILTER is usually the more direct choice. Choose based on whether the result should be an interactive view, a static action, or a live formula output.
Frequently Asked Questions
Do I need ARRAYFORMULA with FILTER in Google Sheets?
No. FILTER already accepts ranges and returns an expanding array. Use ARRAYFORMULA for suitable range-wide calculations that FILTER does not perform, not as a required wrapper around FILTER.
How do I filter with two conditions?
Put the two condition ranges after the return range as separate arguments. They work as AND conditions, so a row must meet both tests. Make sure both condition ranges align with the data rows.
How do I make a FILTER formula return nothing instead of #N/A?
FILTER gives #N/A when no rows match. Wrap it in IFNA and provide an alternative such as an empty string or a message. An empty-string alternative can make the sheet look blank, while a message can make the reason clearer.
Why does my FILTER result not expand?
Check the destination cells for existing values or merged cells. A dynamic result needs a clear output area large enough for every matching row and returned column. Move the formula or clear only the cells that are meant to be output space.
Can FILTER search for text that contains a word?
Yes. Use REGEXMATCH as a condition for a contains-style test, and use a case-insensitive pattern such as (?i)word when appropriate. Remember that regular-expression symbols can affect how a pattern is interpreted.