Excel: Replace Multiple Filter Criteria with a Single Row of Formulas

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

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.

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

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