How to Use FILTER in Excel With Two Conditions and a No-Match Message

Excel's FILTER function returns every row of a range that meets a condition. This guide shows the syntax, how to require two conditions at once, how to sort the result, and how to show your own message when nothing matches. All examples are based on Microsoft's FILTER function page.

One limit up front: this guide does not cover which Excel versions include FILTER. If the function isn't recognised in your copy of Excel, check Microsoft's support article for availability.

The FILTER syntax in one line

=FILTER(array,include,[if_empty])

• array is the range you want to filter, for example A5:D20.
• include is the test that decides which rows to keep, such as C5:C20=H1.
• if_empty is optional. It is what FILTER shows when no rows pass the test.

You only type the formula in one cell. According to Microsoft, the result spills: when you press Enter, Excel creates an array range of the right size to hold the matching rows.

Filter on one condition

1. Put the value you want to match in a cell, for example H1.
2. Click an empty cell with enough space below and to the right for the results.
3. Type a formula that compares one column to that cell:

=FILTER(A5:D20,C5:C20=H1,"")

4. Press Enter. The matching rows spill into the cells below.

Using a cell reference like H1 instead of typing the value into the formula means you can change the criterion without editing the formula. A drop-down list in H1 works well here.

Require two conditions (AND) and sort the result

Microsoft's page gives an example that returns rows where the product is Apples and the region is East, sorted by Units in descending order. Before you enter the formula, set up its two criteria cells:

1. In H1, type the value to match in column C (the first test in the formula).
2. In H2, type the value to match in column A (the second test).
3. Click an empty cell with room below and to the right, then enter:

=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

Here is what each part does:

• (C5:C20=H1)*(A5:A20=H2) puts each condition in its own brackets and multiplies them. A row is kept only when both tests are true.
• "" is the if_empty argument, so the formula returns blank text instead of an error if no row matches.

To add a third condition, add another bracketed test with another *.

Microsoft's page also includes a second example that filters by product or region. Check the official page for that formula.

Show a message when nothing matches

If you leave out if_empty and no row passes the test, FILTER returns an error. Microsoft's page explains that this happens because Excel doesn't support empty arrays.

The fix is to always supply the third argument. Use blank text or a short message:

=FILTER(A5:D20,C5:C20=H1,"No matches")

Why FILTER returns an error when your data has errors

Microsoft notes that if any value in include is an error, such as #N/A or #VALUE, or can't be converted to a Boolean (TRUE or FALSE), FILTER returns an error. if_empty won't catch this, because the problem is in the test, not in an empty result.

To fix it, find and correct the error cells in the column your condition checks. A broken lookup feeding that column is a common cause. If you use XLOOKUP to fill that column, give it a not-found value so it doesn't return #N/A.

Filtering data in another workbook

Microsoft says Excel has limited support for dynamic arrays between workbooks. A FILTER that points at another file works only when both workbooks are open.

If you only need a total of the matching rows rather than the rows themselves, SUMIFS is the simpler tool.