To filter rows by multiple criteria in Excel, use the FILTER function and combine conditions with multiplication for AND or addition for OR. With records in A2:D7, region in column A, product in column C, and criteria in H2:H3, the formula is =FILTER(A2:D7,(A2:A7=H2)*(C2:C7=H3),"No matches"); it returns rows matching both criteria.
How FILTER works
FILTER returns the rows or columns from a range that meet a logical test. Its basic syntax is =FILTER(array,include,[if_empty]): the array is the data to return, include is the condition or conditions to evaluate, and if_empty is an optional value to show when nothing qualifies. The result spills into nearby cells, so the cells where results need to appear must be clear.
For the opening example, assume row 1 contains headers and rows 2–7 contain these fields: Region in A, Rep in B, Product in C, and Sales in D. Put West in H2 and Printer in H3. The formula checks each row and returns all four columns for rows where the region is West and the product is Printer. It returns the records for Ben, with sales of 720, and Dev, with sales of 510. The other West or Printer records fail one of the two tests.
The include argument needs a true-or-false result for each row in the source data. A range comparison such as A2:A7=H2 creates that series of results. The multiplication and addition operators then combine those series row by row.
Use multiplication for AND criteria
Multiplying logical tests makes a row pass only when every test is true. In Excel calculations, TRUE behaves like 1 and FALSE like 0, so a row passes when its combined result is 1. This is useful when a reader needs to narrow results by several fields, such as region, product, and a minimum sales amount.
Assume the same A2:D7 layout, with West in H2, Printer in H3, and 500 in H4. To return only rows satisfying all three conditions, use =FILTER(A2:D7,(A2:A7=H2)*(C2:C7=H3)*(D2:D7>=H4),"No matches"). From the sample records, the result is Ben’s 720 sale and Dev’s 510 sale; Fran’s 480 sale is excluded because it is below the threshold.
Parentheses make the individual tests clear and help avoid precedence mistakes. Keep the compared ranges the same height as the data range: if the array is A2:D7, each row-based test should cover rows 2 through 7. A mismatched range size can produce a calculation error rather than a filtered list.
Use addition for OR criteria
Addition combines conditions as OR: a row qualifies if at least one condition is true. For example, to return records handled by either Ben or Dev, assume reps are in B2:B7, the records to return are A2:D7, and H2 contains Ben while H3 contains Dev. Use =FILTER(A2:D7,(B2:B7=H2)+(B2:B7=H3),"No matches"). This returns Ben’s and Dev’s records, even though their regions or products do not have to be the same.
Use parentheses around each comparison before adding it. If you combine several OR tests, addition can produce 2 or more when a row matches multiple tests; FILTER still treats any nonzero result as included. If you instead need rows to match every listed criterion, multiply the tests rather than add them.
Combine AND and OR with parentheses
Real requests often mix the two patterns. Suppose a user wants records for West where the product is either Printer or Monitor. With the same A2:D7 data, West in H2, Printer in H3, and Monitor in H4, use =FILTER(A2:D7,(A2:A7=H2)*((C2:C7=H3)+(C2:C7=H4)),"No matches"). The outer multiplication requires West, while the grouped addition accepts either product.
Grouping is essential because it shows which alternatives belong together. In plain language, the test says “West AND (Printer OR Monitor).” Without the parentheses around the product alternatives, Excel may evaluate a different combination of conditions than the one intended. Before using a long formula, write the rule in words and mark the AND and OR groups.
Filter by numbers, dates, and text
Criteria can compare values using operators such as greater than, less than, and not equal to. For example, the sales test D2:D7>=H4 in the earlier formula includes sales equal to the threshold as well as larger sales. To exclude equal values, use > instead. For dates, Excel stores actual dates as numbers, so comparisons such as on or after a date work when the cells contain real Excel dates rather than text that merely looks like a date.
For a contains-text search, assume products are in C2:C7 and H2 contains a search word such as “print.” Use =FILTER(A2:D7,ISNUMBER(SEARCH(H2,C2:C7)),"No matching products"). SEARCH finds the text within each product name and ISNUMBER converts a successful position into TRUE. SEARCH is not case-sensitive, so this method can find “Print” and “print” alike. If the criterion should match the entire cell, an equality comparison such as C2:C7=H2 is simpler.
Ordinary equality comparisons in Excel text are generally case-insensitive. If capitalization must match exactly, assume names or codes are in B2:B7 and the required text is in H2; use =FILTER(A2:D7,EXACT(B2:B7,H2),"No exact match"). EXACT performs a case-sensitive comparison. Check that imported values do not contain leading or trailing spaces, since those can also prevent an apparent match.
Make a criterion optional
A common follow-up is how to let a blank criteria cell mean “do not filter by this field.” An empty H2 compared with a region column would normally match blank region cells, not every region. You can explicitly treat the empty input as a match for all rows.
Assume regions are in A2:A7, products in C2:C7, data in A2:D7, and H2 and H3 hold optional region and product criteria. Use =FILTER(A2:D7,((H2="")+(A2:A7=H2))*((H3="")+(C2:C7=H3)),"No matches"). If H2 is blank, its first grouped test is true for each row; otherwise, the region must match. The same rule applies to H3 and product. This makes one formula usable with either, both, or neither criterion filled in.
Be deliberate about what a blank means in your workbook. Sometimes blank should mean “ignore this criterion,” as above; in other cases, the task is specifically to find rows whose source field is blank. Those are different requirements and should use different tests.
Spill results and handle no matches
FILTER is a dynamic array function: it returns as many rows as qualify and spills them from the formula cell. Do not place other values in the expected spill area. If Excel shows a #SPILL! error, select the error indicator to identify cells blocking the output, then clear or move those cells. A filtered result may change size when source data or criteria change.
The third argument in the examples, such as “No matches,” is the value returned when no rows qualify. It is not a blank result and it does not suppress unrelated errors. If the argument is omitted and there are no qualifying rows, FILTER returns a #CALC! error. Choose a message that makes sense for the report, or use an empty string if a visually blank result is preferred. With A2:D7 as the data and H2/H3 as the criteria cells used earlier, the empty-string version is =FILTER(A2:D7,(A2:A7=H2)*(C2:C7=H3),"").
When source data is stored as an Excel Table, table references can make formulas easier to read and automatically adjust as table rows are added. For instance, if a table is named SalesData and has Region and Product columns, a formula can refer to those column names instead of fixed ranges. Use the table’s actual name and exact column headings, and place the spilled formula outside the table; dynamic array formulas cannot spill within a table’s body.
Version support and alternatives
FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. The examples here use comma separators, as is typical in English-language Excel installations; regional settings may use semicolons instead. On Mac, the function works in supported Excel versions too, though menus and keyboard shortcuts can differ. In Excel for the web, formulas are entered in the worksheet as usual.
Excel 2019 and earlier perpetual versions do not include the FILTER function. For a one-time filtering task, use the built-in filter controls: select the data and choose Data > Filter, then apply criteria from the relevant column dropdowns. For more repeatable transformation work, Power Query is another option. Older formula-based solutions can be built with functions such as INDEX and AGGREGATE, but they are longer and require careful handling; they are not direct substitutes with the same simple spill behavior.
Common formula problems
- Wrong rows appear: Check whether the rule needs AND or OR, verify the parentheses, and confirm that each criterion cell contains the intended value.
- #VALUE! or another calculation error: Ensure the include arrays and source array cover the same number of rows. Also inspect any functions inside the condition for errors in source cells.
- #SPILL!: Clear cells in the intended output area, including cells that look empty but contain spaces or formulas returning empty text.
- No match despite similar-looking text: Check for spaces, inconsistent data types, and dates or numbers stored as text. Exact comparisons do not treat text “500” and numeric 500 as identical in every context.
- Results omit new records: Consider converting the source range to a Table, or adjust the formula ranges to cover the added rows.
It is also easy to confuse a FILTER formula with the column dropdown filters available from Data > Filter. The worksheet function returns a separate, formula-driven result that can update when its inputs change. The dropdown feature hides rows in the original list and is useful for interactive inspection, but it does not create a separate spilled result.
Frequently Asked Questions
Can FILTER use multiple criteria in Excel?
Yes. Put a comparison for each criterion in the include argument. Multiply the comparisons for AND conditions and add them for OR conditions, using parentheses to group mixed logic.
How do I filter for one of several values?
Compare the field to each acceptable value and add those tests. A row is returned if any comparison is true. If another condition must also be met, multiply that grouped OR test by the additional condition.
Why does my FILTER formula return #CALC!?
A common cause is that no rows match and the optional if_empty argument was left out. Add a message or another chosen fallback as FILTER’s third argument, and confirm that the criteria and source values really correspond.
Can I use wildcards with FILTER?
Not by placing a wildcard in a direct equality test. For a contains-style condition, SEARCH inside ISNUMBER is a practical option; it is case-insensitive. For a case-sensitive full-cell match, use EXACT.
Does FILTER work in Excel 2019?
No. FILTER is available in Microsoft 365 and Excel 2021 or later, not Excel 2019. Use Data > Filter for interactive filtering, Power Query, or a more involved legacy formula approach.
Related
- Excel Engineering Number Format Explained
- Excel Employee Work Schedule: Setup and Tips
- Excel EIN Number Format: Keep All 9 Digits Visible
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LET and LAMBDA: How They Work Together in FormulasExcel’s LET function assigns names to values inside a formula, while LAMBDA lets you create a reusable function…
- Excel SUMIFS Between Dates: Formula, Examples, and TipsTo sum values in Excel for dates from a start date through an end date, use =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"="&E2,A2:A100,"=E2": that…
- Excel Formula List: Essential Functions With ExamplesAn Excel formula list is a practical reference for choosing the right calculation: use =SUM(B2:B6) to total a…
- XLOOKUP Function Examples: Exact, Approximate, and MoreXLOOKUP searches one range for a value and returns the corresponding value from another range. For example, with…
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in…
- Excel COUNTIF Over a Number: Greater Than CriteriaTo count numbers greater than a value in Excel, use =COUNTIF(A2:A100,">50"), replacing the range and 50 with your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.