FILTER

We introduced FILTER in Lesson 18 as part of the dynamic array overview. Now we go deep. FILTER is one of the most transformative functions in modern Excel — it replaces manual filtering, helper columns, and complex SUMIF workarounds with a single live formula that extracts exactly the rows you need, updates automatically as data changes, and chains cleanly with SORT, UNIQUE, XLOOKUP, and other dynamic array functions. This lesson covers multi-condition filtering, nested FILTER patterns, and the full range of real-world use cases that make FILTER indispensable in professional spreadsheet work.

FILTER Syntax — A Quick Recap

=FILTER(array, include, [if_empty])

  array     →  The range to return (can include multiple columns)
  include   →  A TRUE/FALSE condition array — which rows pass the filter
  if_empty  →  What to display if no rows match (optional but recommended)

Simple example — all rows where Region = "North":
=FILTER(A2:D20, C2:C20="North", "No results")

The key insight: the include argument is an array of TRUE and FALSE values — one per row. Any row where the condition evaluates to TRUE is included in the result. Any row where it is FALSE is excluded. Everything else in FILTER builds on this foundation.

AND Conditions — All Must Be True

To filter by multiple conditions where every condition must be satisfied, multiply the condition arrays together. Because TRUE = 1 and FALSE = 0, multiplying two condition arrays produces 1 only where both are true — and 0 everywhere else.

Sales data: A=Rep, B=Quarter, C=Region, D=Sales

North region AND Q1 only:
=FILTER(A2:D20, (C2:C20="North") * (B2:B20="Q1"), "No results")

North region AND sales over £100,000:
=FILTER(A2:D20, (C2:C20="North") * (D2:D20>100000), "No results")

Three conditions — North, Q1, and sales over £80,000:
=FILTER(A2:D20, (C2:C20="North") * (B2:B20="Q1") * (D2:D20>80000), "No results")

OR Conditions — At Least One Must Be True

For OR logic — include the row if any one of several conditions is true — add the condition arrays instead of multiplying them. Because TRUE = 1, adding two conditions gives a non-zero result if either or both are true, and zero only if both are false.

North OR East region:
=FILTER(A2:D20, (C2:C20="North") + (C2:C20="East"), "No results")

Sales over £100K OR in Q1:
=FILTER(A2:D20, (D2:D20>100000) + (B2:B20="Q1"), "No results")

Three OR options — North, East, or South:
=FILTER(A2:D20, (C2:C20="North") + (C2:C20="East") + (C2:C20="South"), "No results")

Mixed AND + OR Conditions

Real business filters often combine both types of logic — for example, "rows where region is North or East, AND sales exceed £80,000." You can mix * and + in the include argument using parentheses to control precedence, just like in regular arithmetic.

North or East region, AND sales over £80,000:
=FILTER(A2:D20,
  ((C2:C20="North") + (C2:C20="East")) * (D2:D20>80000),
  "No results")

Read it as: (North OR East) AND (sales > 80K)
The outer parentheses around the OR group ensure it is evaluated first,
then the result is multiplied by the sales condition.

Wrapping the OR group in an extra set of parentheses is essential here. Without them, multiplication has higher precedence and the formula evaluates as North AND (East OR sales condition) — which is not what you want. When mixing * and +, always use parentheses to make the intended grouping explicit.

Filtering by a List — ISNUMBER + XMATCH

What if you want to filter rows where a value appears in an approved list — for example, showing only rows for a specific set of regions or product codes? Combining ISNUMBER and XMATCH inside FILTER is the cleanest way to do this.

Approved regions list in G2:G3: {"North", "East"}
Sales data in A2:D20

Filter to only show approved regions:
=FILTER(A2:D20, ISNUMBER(XMATCH(C2:C20, G2:G3)), "No results")

How it works:
  XMATCH(C2:C20, G2:G3)  →  returns a position number for each match, #N/A for no match
  ISNUMBER(...)           →  converts to TRUE/FALSE (TRUE = found in approved list)
  FILTER uses that TRUE/FALSE array as the include condition

This pattern is far more maintainable than a long chain of OR conditions. When your approved list changes, you update the list in G2:G3 — the FILTER formula needs no changes at all. It is also cleaner than COUNTIF(list, value)>0, which is the older equivalent.

FILTER with Wildcards — ISNUMBER + SEARCH

FILTER's include argument does not natively support wildcards. But you can achieve wildcard-style filtering by using SEARCH (which supports wildcards and returns a position number or error) inside ISNUMBER — the same pattern as the list lookup above.

Product names in B2:B20

Filter to rows where product name contains "Pro":
=FILTER(A2:D20, ISNUMBER(SEARCH("Pro", B2:B20)), "No results")

Filter to rows where name starts with "Widget":
=FILTER(A2:D20, ISNUMBER(SEARCH("Widget*", B2:B20)), "No results")

Case-insensitive — SEARCH ignores case by default.
For case-sensitive matching use FIND instead of SEARCH.

SEARCH returns the starting position of the search text if it finds a match, and an error if it does not. Wrapping it in ISNUMBER produces a clean TRUE/FALSE array that FILTER can use directly. This is one of the most useful FILTER patterns for text-based data.

Chaining FILTER With SORT and UNIQUE

Because FILTER returns an array, you can wrap it in SORT to sort the results, UNIQUE to deduplicate them, or both. These chains build complete data retrieval pipelines in a single formula — no helper columns, no manual steps.

North region rows, sorted by sales descending:
=SORT(FILTER(A2:D20, C2:C20="North"), 4, -1)

Unique list of regions that have sales over £100K:
=UNIQUE(FILTER(C2:C20, D2:D20>100000))

Sorted unique regions with sales over £100K:
=SORT(UNIQUE(FILTER(C2:C20, D2:D20>100000)))

Top 5 earners across the whole dataset:
=SORT(A2:D20, 4, -1)  →  all rows sorted descending
Wrap with INDEX to return just the first 5:
=INDEX(SORT(A2:D20, 4, -1), SEQUENCE(5), {1,2,3,4})

Dynamic Filtering With Cell References

One of the most powerful patterns in production dashboards is connecting a FILTER formula to a dropdown or input cell — so changing the value in one cell instantly updates an entire filtered table. This creates a genuinely interactive report without any macros or VBA.

Region selector in cell F1 (Data Validation dropdown)
Sales data in A2:D50

Dynamic filter — table updates when F1 changes:
=FILTER(A2:D50, C2:C50=F1, "No results for: "&F1)

Dynamic filter with a fallback message showing the selected value:
=IFERROR(
  FILTER(A2:D50, C2:C50=F1),
  "No data found for region: "&F1
)

Dynamic AND filter — region from F1, quarter from F2:
=FILTER(A2:D50, (C2:C50=F1) * (B2:B50=F2), "No results")

Aggregating FILTER Results

Because FILTER returns an array, you can wrap it in aggregate functions to calculate totals, counts, and averages on the filtered subset — all without SUMIFS or helper columns.

Sales data: A=Rep, B=Quarter, C=Region, D=Sales

Total North region sales:
=SUM(FILTER(D2:D20, C2:C20="North"))

Average North region sales:
=AVERAGE(FILTER(D2:D20, C2:C20="North"))

Count North region reps:
=COUNTA(FILTER(A2:A20, C2:C20="North"))

Highest sale in North region:
=MAX(FILTER(D2:D20, C2:C20="North"))

All of the above in a single column — dynamic summary panel:
=SUM(FILTER(D2:D20, C2:C20=F1))        Total
=AVERAGE(FILTER(D2:D20, C2:C20=F1))    Average
=MAX(FILTER(D2:D20, C2:C20=F1))        Best
=COUNTA(FILTER(A2:A20, C2:C20=F1))     Count

Next up — SORT and SORTBY, where you will learn how to sort by multiple columns, sort by a column outside the return range, and build ranked leaderboards and dynamic sorted views that update automatically.