Excel Dynamic Filter Multiple Criteria: Formulas and Tips

Formulas & functions · Updated November 19, 2026

To dynamically filter rows in Excel by multiple conditions, use the FILTER function and combine criteria with multiplication for AND or addition for OR. For example, with data in A2:D100, regions in B2:B100, and products in C2:C100, =FILTER(A2:D100,(B2:B100="West")*(C2:C100="Laptop"),"No matches") returns rows where the region is West and the product is Laptop; the results update when the source data changes.

What dynamic filtering means in Excel

A dynamic filter is a formula that returns matching records in a worksheet range and adjusts its output as the source data or criteria change. Unlike the ordinary filter buttons on the Data tab, a FILTER formula creates a separate, live result range. It does not hide nonmatching rows in the original list.

FILTER is available in Excel for Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also available in Excel for the web. The formula syntax and spill behavior are broadly the same in the Windows and Mac desktop apps. Older Excel versions that do not support dynamic arrays cannot calculate this function; use the built-in filters, Advanced Filter, or a compatible alternative instead.

The function’s basic form is =FILTER(array,include,[if_empty]). The array is the range to return, include is a TRUE/FALSE test for each source row, and the optional third argument supplies a result when nothing qualifies. The include test must line up with the rows in the array. If A2:D100 is the array, a condition based on B2:B100 has the matching number of rows.

AND criteria across different columns

In Excel formulas, multiplying TRUE/FALSE tests works as AND: TRUE is treated as 1 and FALSE as 0, so only rows where every test is TRUE produce 1. With dates in column A, regions in B, products in C, and sales in D, this formula requires both a West region and a Laptop product:

With records in A2:D100, regions in B2:B100, and products in C2:C100, use:

=FILTER(A2:D100,(B2:B100="West")*(C2:C100="Laptop"),"No matches")

For a concrete example, suppose row 2 is 1/5/2026, West, Laptop, 1200; row 3 is 1/7/2026, East, Laptop, 900; row 4 is 1/10/2026, West, Monitor, 300; and row 5 is 1/12/2026, West, Laptop, 1500. This formula returns rows 2 and 5, including all four columns. Row 3 fails the region test, and row 4 fails the product test. The third argument displays “No matches” instead of an empty-result error when no row qualifies.

You can add more AND conditions by multiplying another same-height test. For example, adding a sales threshold keeps only rows above the chosen amount:

With sales in D2:D100 and a minimum amount in F2, use:

=FILTER(A2:D100,(B2:B100="West")*(C2:C100="Laptop")*(D2:D100>F2),"No matches")

Here the criteria must all be true: West, Laptop, and sales greater than F2. If F2 contains 1000, the sample data returns the row with sales of 1200 and the row with sales of 1500.

OR criteria and mixed logic

Adding TRUE/FALSE tests works as OR: a row qualifies if the sum is greater than zero. For example, the following returns a record if its region is West or its product is Laptop. These are alternatives, not requirements that both conditions be true.

With regions in B2:B100 and products in C2:C100, use:

=FILTER(A2:D100,((B2:B100="West")+(C2:C100="Laptop"))>0,"No matches")

For the sample records, this returns rows 2, 3, 4, and 5: row 3 qualifies because it is a Laptop, even though its region is East. A row that is neither West nor Laptop is excluded. The explicit >0 makes the OR test clear; Excel can also treat a nonzero sum as TRUE.

For mixed logic, use parentheses to make the intended groups clear. For example, “West and either Laptop or Monitor” means the region condition must be true, while at least one product condition is true:

With regions in B2:B100 and products in C2:C100, use:

=FILTER(A2:D100,(B2:B100="West")*((C2:C100="Laptop")+(C2:C100="Monitor")>0),"No matches")

The parentheses determine how Excel evaluates the logic. In complex formulas, write down the rule in words first, then group each AND or OR section explicitly. This helps avoid a formula that returns plausible-looking but incorrect records.

Let readers change criteria in cells

Hard-coded conditions such as "West" work, but placing criteria in worksheet cells makes a report easier to reuse. If F2 contains the region and G2 contains the product, changing either value updates the results without editing the formula.

With source data in A2:D100, regions in B2:B100, products in C2:C100, and criteria in F2 and G2, use:

=FILTER(A2:D100,(B2:B100=F2)*(C2:C100=G2),"No matches")

Sometimes a blank criteria cell should mean “ignore this condition.” Use an OR test between “the criteria cell is blank” and “the row matches the criterion.” Multiplying those grouped tests means that every supplied criterion must match, while each blank one is skipped.

With the same source columns and optional region and product criteria in F2 and G2, use:

=FILTER(A2:D100,((F2="")+(B2:B100=F2))*((G2="")+(C2:C100=G2)),"No matches")

If F2 is blank and G2 contains Laptop, the formula returns all Laptop rows regardless of region. If both cells are blank, it returns all rows in the source range. Put a prompt beside each criteria cell so users know which values to enter; typing a space is not the same as leaving a cell blank.

Filter text, numbers, and dates

Exact comparisons are often the safest choice for categories such as region or product. To find text that contains a phrase rather than an exact match, combine SEARCH with ISNUMBER. SEARCH is not case-sensitive, and it returns a position when it finds the text; ISNUMBER converts that result into a TRUE/FALSE test for FILTER.

With products in C2:C100, the text to find in F2, and the return range in A2:D100, use:

=FILTER(A2:D100,ISNUMBER(SEARCH(F2,C2:C100)),"No matches")

If F2 contains “lap,” this can match a product name such as “Laptop.” For a case-sensitive search, use FIND instead of SEARCH. If the search cell is blank, SEARCH treats an empty string as a match, so this pattern can return every row; use the optional-criteria pattern or require a nonblank input if that is not desired.

For dates, compare actual Excel date values rather than text that merely looks like a date. A useful pattern for a period is to include the start date and exclude the day after the period ends. That also handles dates that include times.

With dates in A2:A100, a start date in F2, and an end date in G2, use G2 as the day after the final date and enter:

=FILTER(A2:D100,(A2:A100>=F2)*(A2:A100<G2),"No matches")

For example, to include every date in January 2026, set F2 to January 1, 2026, and G2 to February 1, 2026. A comparison using “less than February 1” includes timestamps throughout January 31 without needing to guess the final time value.

Where results appear and how to prepare the source

Enter a FILTER formula in a cell with enough empty space below it and, if multiple columns are returned, to its right. Excel spills the matching rows into neighboring cells automatically. The formula itself remains in the top-left cell of that output. You cannot type into an individual spilled result cell; edit the source data, criteria, or formula instead.

  • Keep the source columns consistent: each condition range must have the same number of rows as the returned array.
  • Use a bounded range such as A2:D1000 rather than full-column references when practical, especially in a large workbook.
  • Keep headers outside the data range if you do not want them returned as a matching record.
  • Consider converting a growing list into an Excel Table. Table columns expand as records are added, which helps keep a report current; enter the spill formula outside the table because spilled arrays cannot expand inside a table.

When a formula returns no matches, its optional third argument can provide a readable message. That argument must be a single value or text, not a second filter range. It is not a substitute for correcting an invalid formula or mismatched ranges.

Common errors and ways to fix them

  • #SPILL! means Excel cannot place the complete result in the cells it needs. Clear any obstructing value or merged cell in the spill area, and make sure the formula is not inside an Excel Table.
  • #CALC! can appear when there are no matches and the optional if_empty argument was omitted. Add a third argument such as "No matches" if a message is appropriate.
  • #VALUE! or unexpected results can indicate that the include array and the returned array have different dimensions. Check that their first and last row numbers agree.
  • Unexpected missing matches may come from trailing spaces, inconsistent spelling, numbers stored as text, or dates stored as text. Clean or standardize the source values, and check the actual cell contents rather than only their display format.
  • Wrong AND/OR behavior usually points to missing or misplaced parentheses, or to using addition where multiplication was intended. Test each condition separately in a spare column if you need to see which rows pass each test.

Also check that the formula uses commas between arguments, as shown here, and that quotation marks around text are straight double quotes. Some regional Excel settings use semicolons as formula separators; if Excel rejects a formula with commas, your installation may expect its regional list separator instead.

FILTER formulas compared with built-in filters

The FILTER function is especially useful for a dashboard, a second-sheet report, or a result that should respond immediately to criteria cells. It leaves the original list intact and calculates a separate output. It also makes AND, OR, and mixed conditions explicit in the formula, which can be useful when a report needs repeatable logic.

The built-in AutoFilter is often more convenient when a person wants to inspect or edit the original list directly. Choose Data > Filter to add filter controls to a range with headers, then use each column’s dropdown to select values or apply text, number, or date filters. You can filter multiple columns together, but the filter controls hide rows in the original range rather than creating a separate spilled result.

Advanced Filter is another built-in option for criteria-based extraction, including copying matching records elsewhere, but it is not a live dynamic-array formula. For a continuously updating result without manually rerunning an extraction, FILTER is usually the more direct choice when the Excel version supports it.

Frequently Asked Questions

Can Excel FILTER use multiple criteria?
Yes. Multiply condition arrays for AND, add them for OR, and use parentheses for mixed logic. Every condition array must correspond row by row with the range being returned.

How do I filter by multiple values in the same column?
Use addition between the equality tests to mean “this value or that value.” For example, a region test for West or East is (B2:B100="West")+(B2:B100="East"). Combine that grouped OR test with other required conditions by multiplying it by them.

Can FILTER criteria be entered in cells?
Yes. Compare the source columns to cells such as F2 and G2 instead of typing fixed values into the formula. The output recalculates when those criteria cells change. Use an optional-criteria pattern if blank cells should mean “show all values for this field.”

Why does FILTER show #SPILL!?
Excel needs an unobstructed area to display every returned row and column. Remove content blocking the spill range, unmerge cells there if necessary, and place the formula outside an Excel Table.

Does FILTER work in older Excel versions?
FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, and in Excel for the web. Excel 2019 and earlier do not include the dynamic-array FILTER function, so use AutoFilter or Advanced Filter, or open the workbook in a supported version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.