FILTER with multiple OR criteria

To extract data with multiple OR conditions, you can use the FILTER function together with the MATCH function. In the example shown, the formula in F9 is: =FILTER(B5:D16, ISNUMBER(MATCH(items,F5:F6,0))* ISNUMBER(MATCH(colors,G5:G6,0))* ISNUMBER(MATCH(cities,H5:H6,0))) where items (B3:B16), colors (C3:C16), and cities (D3:D16) are named ranges. This formula returns data where item is (tshirts OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle).

April 14, 2025 · 1 min · 84 words · John Rodriguez

Table of Contents

Explanation

In this example, criteria are entered in the range F5:H6.

MATCH is configured “backwards”, withlookup_valuescoming from the data, and criteria used for thelookup_array.

For example, the first condition is that items must be either a Tshirt or Hoodie.

Excel formula: Filter text contains

Notice numbers correspond to items that are either Tshirt or Hoodie.

The output from FILTER is dynamic.

If source data or criteria change, FILTER will return a new set of results.

Excel formula: Filter this or that

MATCH supports approximate and exact matching, andwildcards(* ?)

Often, MATCH is combined with the…

Excel FILTER function

Excel ISNUMBER function

Excel MATCH function