Replace Multiple Filter Criteria with a Single Row of Formulas

September 16, 2022 - by Bill Jelen

Problem: The Advanced Filter feature can handle combinations of criteria, but I have a particular situation where I want all records where the customer, industry and product come from these lists. To list all combinations of five products, five customers, and three industries would require 75 rows of combinations. Is there an easier way?

Strategy: You can replace traditional criteria with a formula-based criteria range. To use a formula-based condition, leave the heading row of the criteria range blank. Write a logical formula in the criteria range that tests the first row of the data set. This formula will be applied to all rows of the data set.

One of the coolest uses of Advanced Filter - you need all records that are one of five products, five customers, and three markets. That would require a very tall criteria range with 5 times 5 times 3 of 75 tedious combinations plus a heading row. Instead of the 76-row criteria range, simply list the f products in M2:M6, the 5 customers in N2:N6 and the markets in M8:M10. — Figure 697. Get all combinations of these three lists.

In the following example, the MATCH looks at the first product in cell C2 and sees if it is in the list of products in M2:M6. Because match returns either the matching row number or an #N/A! error, the formula tests for #N/A! and then reverses the result using NOT. =NOT(ISNA(MATCH(C2,$M$8:$M$10,0))).

Similar formulas in I2 and J2 test for customers and industries.

I2: =NOT(ISNA(MATCH(B2,M2:M6,0)))

J2: =NOT(ISNA(MATCH(E2,$N$2:$N$6,0)))

The criteria range will be three blank cells in I1:K1 and three MATCH or VLOOKUP formulas in I2:K2. The blank top row is the signal to Excel that these are formula-based criteria. — Figure 698. Heading row blank, formula in row 2.

When you perform the Advanced Filter, specify I1:K2 as the criteria range. Excel will apply the formulas to each row of your dataset and only return the records where all three formulas evaluate to TRUE.

This article is an excerpt from Power Excel With MrExcel

Title photo by Matthew Wheeler on Unsplash