=FILTER(array, include, [if_empty]) FILTER dynamically extracts matching rows from a dataset based on one or more criteria. For AND criteria, multiply conditions with *. For OR criteria, add conditions with +.
FILTER Function in Excel & Google Sheets
The FILTER function dynamically filters a dataset based on one or more logical rules and spills the matching results into adjacent rows and columns. When the source data changes, the filtered output updates in real time.
1. Basic Single Criterion Filter
=FILTER(A2:D10, C2:C10 = "Marketing", "No records found")2. Multiple Conditions (AND Logic)
To filter for rows where Department is “Engineering” AND Salary is greater than $100,000:
=FILTER(A2:E50, (D2:D50 = "Engineering") * (E2:E50 > 100000), "No matches")3. Multiple Conditions (OR Logic)
To filter for employees in either “New York” OR “San Francisco”:
=FILTER(A2:E50, (C2:C50 = "New York") + (C2:C50 = "San Francisco"), "No matches")Frequently Asked Questions
How do I filter by multiple AND conditions?
Multiply the boolean conditions with an asterisk (*): =FILTER(A2:D100, (B2:B100="Sales") * (C2:C100 > 50000)).
How do I filter by OR conditions?
Add the boolean conditions with a plus sign (+): =FILTER(A2:D100, (B2:B100="East") + (B2:B100="West")).