Yes. In Microsoft Excel for Microsoft 365, use the FILTER function with multiplication between conditions for AND logic, or addition for OR logic. For example, with data in A2:D6, regions in A and statuses in B, this returns rows where the region is West and the status is Open: =FILTER(A2:D6,(A2:A6="West")*(B2:B6="Open"),"No matches").
What FILTER Does
FILTER returns matching rows or columns from a range or array. Unlike a traditional filter that hides nonmatching rows in place, its result appears as a separate, dynamically updating list. The formula has three arguments: the source array, an include expression that identifies what to keep, and an optional value to return when nothing matches.
The function is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. It is not available in Excel 2019 or earlier as a native worksheet function. The formulas below use commas as argument separators, which is the default in many installations; some regional settings use semicolons instead.
For multiple criteria, the include expression combines one or more TRUE/FALSE tests. Excel treats TRUE as 1 and FALSE as 0 in the arithmetic expressions used here. Multiplication acts like logical AND: every multiplied test must be TRUE for the result to be included. Addition acts like logical OR: at least one added test must be TRUE.
Set Up the Data and Criteria
Assume a small worksheet has headers in row 1 and records in rows 2 through 6. Column A contains Region, column B Status, column C Item, and column D Amount. The five records are West / Open / Widget / 1200; East / Open / Gadget / 800; West / Closed / Widget / 1500; West / Open / Gadget / 900; and North / Open / Widget / 1100.
The source range and each criteria range need the same number of rows. Here, the source is A2:D6 and both criteria ranges are two five-row ranges: A2:A6 and B2:B6. Use data rows, not the header row, for these comparisons. Put the formula in an empty area with room for its results to spill into adjacent cells.
The criteria can be typed into a formula, as in the opening example, or stored in worksheet cells so the user can change them without editing the formula. If F2 contains West and G2 contains Open, the layout is still A2:D6 for the source, with regions in A2:A6, statuses in B2:B6, and criteria in F2 and G2.
Use AND for Conditions That Must All Match
For the West and Open example, each row is tested twice. The first comparison checks whether its Region is West; the second checks whether its Status is Open. Multiplying the two arrays keeps only rows for which both tests are TRUE.
Assuming the same A2:D6 layout, with regions in A2:A6 and statuses in B2:B6, this formula returns the first and fourth data records: the West Open Widget row and the West Open Gadget row. Their source row numbers are 2 and 5, and the other columns remain attached to each matching record.
=FILTER(A2:D6,(A2:A6="West")*(B2:B6="Open"),"No matches")
For a longer condition, multiply another test into the include expression. With Amount in D2:D6, this version requires West, Open, and an amount of at least 1,000. It returns only the row with an amount of 1,200: the West Open Widget record. A row meeting only two of the three conditions is excluded.
=FILTER(A2:D6,(A2:A6="West")*(B2:B6="Open")*(D2:D6>=1000),"No matches")
When criteria are in cells, they can be changed independently. Assuming F2 contains the requested region and G2 the requested status, while the source remains A2:D6, use:
=FILTER(A2:D6,(A2:A6=F2)*(B2:B6=G2),"No matches")
Use OR for Conditions Where Either Can Match
Use addition when a row should qualify if it meets one condition or another. Suppose the goal is to return records for either West or East, regardless of status. With the same source data and region values in A2:A6, the formula below returns four rows: the two West Open records, the West Closed record, and the East Open record.
=FILTER(A2:D6,(A2:A6="West")+(A2:A6="East"),"No matches")
A row that matches both conditions would produce 2 rather than 1, but it is still included. FILTER needs a nonzero result from the include expression, not a particular value of 1. This is why addition works for OR tests without duplicating a source row.
For several alternatives in one column, you can add more comparisons. Keep the range references the same size as the source. If the list of acceptable values is long or changes often, a separate criteria list may be easier to maintain than a lengthy formula.
Combine AND and OR Conditions
Parentheses determine how a mixed expression is evaluated. For example, to return Open records from either West or East, group the two region comparisons with addition, then multiply that group by the status test. Assuming regions are in A2:A6 and statuses are in B2:B6, the result is the West Open Widget, East Open Gadget, and West Open Gadget records, in their original order.
=FILTER(A2:D6,((A2:A6="West")+(A2:A6="East"))*(B2:B6="Open"),"No matches")
The parentheses are important: they make the logic “(West OR East) AND Open.” Without grouping, the arithmetic expression could represent a different combination of tests. When a formula becomes difficult to read, put each condition on a separate line in the formula bar or build it in smaller pieces before combining the tests.
Use Numeric, Date, and Text Criteria
Comparison operators such as >, >=, <, and <= work in criteria arrays. A numeric condition like D2:D6>=1000 compares the amount on each row with 1,000. Combine it with another test using multiplication for AND or addition for OR. Be sure the cells contain numbers, not numbers stored as text, or comparisons may not behave as expected.
Dates in Excel are stored as serial numbers, so a date condition can compare a date column to a real Excel date. For example, if transaction dates are in E2:E100 and the cutoff date is in G2, use E2:E100>=G2 as one of the include tests. Enter G2 as a date value rather than typing a date inside a text comparison. The date column and the other criteria ranges must align row by row.
Text comparisons are generally not case-sensitive: West matches west. A criterion such as "*widget*" can use asterisks as wildcards when comparing text, where an asterisk represents any sequence of characters. For instance, this kind of test can find descriptions containing the word widget. If you need a case-sensitive test, the ordinary equality comparison is not sufficient; a more specialized formula using EXACT is required.
Handle No Matches and Dynamic Results
The optional third argument prevents a no-match result from showing #CALC!. In the examples, "No matches" is returned if no row satisfies the criteria. You can replace it with another message or a blank string, written as "". This argument handles the case where there are no qualifying rows; it does not fix other errors in the source data or formula.
FILTER returns a dynamic array, so it fills as many neighboring cells as necessary. The result can grow or shrink when source values or criteria change. Do not type into cells where the result needs to spill. If an obstruction blocks the output, Excel displays #SPILL!; clear the blocking cells or move the formula to an area with enough space.
Results from a dynamic array formula should not be placed inside an Excel Table. Put the formula in a normal worksheet cell outside the table, where its output can spill. You can use a Table as the source, however, which is useful because structured references expand as new rows are added.
Use FILTER with an Excel Table
Suppose the source data has been formatted as a Table named SalesData, with columns named Region, Status, Item, and Amount. If F2 contains a region and G2 contains a status, the table formula below returns matching records and expands to include new table rows automatically.
=FILTER(SalesData,(SalesData[Region]=F2)*(SalesData[Status]=G2),"No matches")
To create or rename a Table in Excel for Microsoft 365 on Windows, select a cell in the data and use Insert > Table, then set its name on the Table Design tab. On Mac, the Table Design tab may be labeled Table depending on the Excel release. Use the actual table and column names from the workbook; a misspelled structured reference produces a formula error.
Common Errors and How to Fix Them
- #SPILL! means the output area is blocked, merged, or otherwise unavailable. Clear the cells in the intended spill range or move the formula.
- #CALC! often means there are no matches and the formula omitted its if_empty argument. Add a suitable third argument if that is the expected possibility.
- #VALUE! can occur when the include array does not have the same dimensions as the source array. Check that every criteria range covers the same rows and, for column-based filtering, the same number of columns as required.
- Unexpected records can result from spaces at the start or end of text, inconsistent spelling, or selecting the wrong columns. Compare the actual cell contents and confirm that each criterion refers to the intended field.
A common misconception is that FILTER automatically searches every column for a criterion. It does not: each comparison must explicitly reference the column or array being tested. Another frequent mistake is using multiplication for alternatives. Multiplication requires every condition to be true; use addition when either condition is sufficient.
FILTER Compared with Excel’s Regular Filter
FILTER is useful when the result needs to appear elsewhere, feed another formula, or update automatically as conditions change. It leaves the original data visible and unchanged. Excel’s regular AutoFilter instead hides rows that do not meet selections in the original list, which can be convenient for examining or editing a dataset in place.
To use the regular filter in Microsoft 365 for Windows, select a cell in the dataset and choose Data > Filter. This is not the same as the worksheet function: AutoFilter does not create a spill range, and its selections are made through column dropdowns. Use FILTER for a formula-driven extract; use AutoFilter for interactively narrowing the displayed source list.
Frequently Asked Questions
Can Excel FILTER use multiple criteria?
Yes. Multiply condition arrays for AND logic and add them for OR logic. Use parentheses when combining the two types of logic.
How do I filter with two conditions in Excel?
With the source in A2:D6, regions in A2:A6, and statuses in B2:B6, use =FILTER(A2:D6,(A2:A6="West")*(B2:B6="Open"),"No matches") to return rows satisfying both conditions.
How do I use OR criteria in FILTER?
Add the condition arrays. For example, (A2:A6="West")+(A2:A6="East") includes rows whose region is either West or East.
Why does FILTER return #CALC!?
Usually, no rows match and the optional if_empty argument was left out. Add a third argument such as "No matches" if you want a message instead.
Can I use FILTER with older Excel versions?
FILTER is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 and earlier as a native function. In older editions, use AutoFilter or Advanced Filter for manual extracts, or build a more involved formula or macro solution.
Related
- Excel Wrap Text Shortcut: Keys, Ribbon, and Fixes
- Excel: How to Work Out the Difference Between Two Numbers
- Excel Work Schedule Template: Build and Customize One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
- Excel Data Validation List Formula: Examples and FixesFor an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and…
- SUMPRODUCT in Excel: Multiple Criteria ExplainedYes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example,…
- VLOOKUP Errors in Excel: Causes, Checks, and FixesMost VLOOKUP errors come from a lookup value that does not match the first column of the search…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.