• Excel Book Excerpt

Excel Replace Multiple Filter Criteria with a Single Row of Formulas

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Replace Multiple Filter Criteria
with a Single Row of Formulas

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?

Figure 642 Get all combinations of these three lists.

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.

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))).

Figure 643 Heading row blank, formula in row 2.

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)))

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.

For more resources for Microsoft Excel: