To filter data in an array formula (i.e. to exclude or require certain values), you can use a formula pattern based on the IF, MATCH, and ISNUMBER functions. In the example shown, the formula in H5:


where data (B4:D11) and filter (F4:F6) are named ranges. The COUNT function is used to count results to validate that the filter is working as expected.

Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel.

Generic formula



In this example, the goal is to filter the values in one array by the values in another array. For convenience, data (B4:D11) and filter (F4:F6) are named ranges. The solution is based on the MATCH function with the ISNUMBER function. This is a pattern you will see often in more advanced formulas. The formula in cell H5 is:

  1. The MATCH function checks all values in the named range data against values in filter
  2. ISNUMBER converts matched values to TRUE and non-matched values to FALSE
  3. The IF function uses the output in #2 above to perform the actual filtering.

The array returned by the IF function looks like this:


As you can see, the values in data that match the values in filter have survived. The values that don't match however have been replaced by FALSE.

The COUNT function is only used to verify the result. Because COUNT automatically ignores the logical values TRUE and FALSE, it returns a count of numbers only, which represent matched values.

Filter to exclude

You can easily reverse the logic to exclude values that appear in filter. In cell H6, the formula has been modified to exclude values using 1-ISNUMBER like so:


This effectively reverses the array output in step #2 above.

FILTER function

This example was created before the FILTER function became available. However, the same basic pattern (ISNUMBER + MATCH) works great with FILTER.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.