Google Sheets FILTER function with multiple conditions

Pivot the page, click the funnel icon, pick a value, repeat for the next column — and lose the whole setup the next time someone reopens the sheet. The built-in filter view is fine for one person and one question. The moment you need a second condition, a saved query, or a result other people can read without touching, the FILTER function is the better tool. It is a formula, so it lives in a cell, recomputes on every change, and never needs to be re-applied.

This walks through the patterns that come up after the first =FILTER(range, condition): combining AND with OR, partial-text matching, dropdown-driven criteria, the empty-result fix, and reading the error codes when a formula breaks.

How FILTER reads its conditions

The signature is short: FILTER(range, condition1, [condition2, ...]). Each condition is a column the same height as range that resolves to TRUE or FALSE per row. FILTER keeps the rows where every condition is TRUE. That is the only mental model you need — every advanced pattern in this article is a way to shape one or more boolean columns.

The sample for the rest of the article is a small orders sheet:

A single-condition filter that pulls every shipped order:

=FILTER(A2:E6, D2:D6="Shipped")

A2:E6 is the data, D2:D6="Shipped" is a five-row boolean column. The result spills into the cell where you type the formula and the cells below and to the right. Anchor it in a cell with empty neighbors or you will see #REF! with the message “Array result was not expanded.”

AND, OR, and combining the two

Add a comma between conditions for AND. Add the boolean columns together with + for OR. Multiply with * when you want to bind a group together. Those three operators — ,, +, * — cover every logical shape you will write.

Shipped orders over $1,000 — a pure AND:

=FILTER(A2:E6, D2:D6="Shipped", E2:E6>1000)

Orders from East or South — a pure OR:

=FILTER(A2:E6, (C2:C6="East") + (C2:C6="South"))

Shipped orders from East or South — AND across an OR group. Parentheses matter:

=FILTER(A2:E6, (D2:D6="Shipped") * ((C2:C6="East") + (C2:C6="South")))

Read it left to right: must be shipped, AND must be one of two regions. The grouped + resolves to 1 when either region matches; multiplying by the shipped boolean keeps only the rows where both sides are true. The same logic in spoken English would say “and” twice in three different places, and that ambiguity is exactly why you need the parentheses.

Partial text matches with REGEXMATCH

FILTER’s equality test is strict: "acme" = "Acme" is TRUE (Google Sheets compares text case-insensitively by default in equality), but "Acme Corp" = "Acme" is FALSE. For substring matches, prefix matches, or “any of these names” patterns, wrap the column in REGEXMATCH. It returns a boolean column FILTER will accept directly.

Customers whose name contains “acme” or “globex”, case-insensitive:

=FILTER(A2:E6, REGEXMATCH(B2:B6, "(?i)acme|globex"))

(?i) turns on case-insensitive mode, and | is the regex OR. This replaces a long string of (B2:B6="Acme")+(B2:B6="Globex")+... with one short expression that you can extend without restructuring the formula.

For “starts with”, anchor with ^: REGEXMATCH(B2:B6, "^Ac"). For “ends with”, anchor with $. For numeric ranges you do not need regex — keep using >, <, and <=, which are faster and easier to read.

Drive the filter from a dropdown

Most “interactive dashboard” requests come down to this: one cell holds a dropdown, the filter follows it. Set up a data-validation dropdown in G1 with the region list, then point FILTER at it:

=FILTER(A2:E6, C2:C6=G1)

1. Select G1, open Data › Data validation, add a “Dropdown” rule with the region values.
2. Type the FILTER formula in I1 (or wherever the result block should land). Leave the cells below it empty.
3. Pick a region from G1; the result block updates in place.

The problem with this shape is the empty default. When G1 is blank, no row matches and you get #N/A. The usual fix is to make the dropdown itself an optional filter — show every row when G1 is empty, narrow when it is set:

=FILTER(A2:E6, (G1="") + (C2:C6=G1))

The (G1="") term is a single boolean that broadcasts across every row. When the dropdown is empty it resolves to 1 for the whole column and the OR passes everything through. When the dropdown has a value, that term collapses to 0 and only the rows matching the region survive. The same trick works for two, three, or four optional dropdowns — add one OR term per dropdown. For a deeper look at how Google Sheets evaluates these column-shaped expressions, see array formulas in Google Sheets.

Handle the empty-result case

An empty FILTER result is not an error in the engineering sense — it is the correct answer to the question “no rows matched.” Sheets just renders it as #N/A, which looks broken to anyone reading the dashboard. Wrap the call in IFNA with a human-readable fallback:

=FILTER(A2:E6, C2:C6="North")

=IFNA(FILTER(A2:E6, C2:C6="North"), "No rows match")

Use IFNA rather than IFERROR here. IFERROR swallows every error code, including the #VALUE! you would otherwise see when a condition range is the wrong size — which is exactly the bug you want the formula to surface, not hide. Narrow error handling tells you when something real is broken.

Sort, slice, and combine the result

FILTER returns rows in the order they appear in the source. To rank by amount, wrap the whole call in SORT. To keep only the top three, wrap that in ARRAY_CONSTRAIN. Each wrapper takes an array in and returns an array out, so the composition is direct.

Top three shipped orders by amount, largest first:

=ARRAY_CONSTRAIN(
   SORT(FILTER(A2:E6, D2:D6="Shipped"), 5, FALSE),
   3, 5)

SORT(array, 5, FALSE) sorts by column 5 (the Amount column inside the filtered block, not column E of the sheet), descending. ARRAY_CONSTRAIN(array, 3, 5) keeps the first three rows and all five columns. If you would rather rank-then-limit with SQL-style syntax, the QUERY function does the same job in one call with ORDER BY and LIMIT; FILTER wins when the criteria themselves are awkward to express as a WHERE clause.

FILTER also composes with external data: FILTER(IMPORTRANGE(url, "Sheet1!A2:E"), ...) works once the destination sheet has been authorized. The condition ranges have to reference the same IMPORTRANGE call, which usually means dropping the imported data into a hidden helper range first. The full pattern is covered in syncing data across sheets with IMPORTRANGE.

Read the error, then fix the formula

FILTER has a small, predictable set of error codes. Each one points at a specific shape problem in the formula — once you can name the cause, the fix is usually one edit.

The #VALUE! case is the one that catches almost everyone the first time. FILTER will not silently truncate or extend a condition column to fit — that is by design, since a mismatched length almost always means the formula is pointing at the wrong column. The fix takes ten seconds; the bug it prevents is a silent miscount. The official Google Docs Editors Help entry for FILTER documents the exact-length requirement and the row-vs-column restriction.

Pick one report in a current sheet that you rebuild by hand — toggling filter dropdowns, copying rows to another tab. Replace it with a single FILTER call into an empty block, drive its main criterion from a dropdown cell, and wrap the result in IFNA so it never shows a raw #N/A. That formula will keep working as the data grows, as criteria change, and as other people open the sheet — which is the thing the manual filter view never does.