Advanced Filter Magic by David Hager

 

Previously, I discussed the use of a custom list with Data Advanced Filter.

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

In the article, I only showed one custom list being used in the advanced filter. However, in most situations, a number of specific searches are required. To illustrate this, I combined all of the links from ESL #1-60 in an Excel file. The description of the links are in column A and the links are in column B. The goal is to perform multiple searches with lists while using only a single formula in the advanced filter. A picture of this can be seen in the following figure.

 

The formula in the advanced filter (B1:B2) on the AdvFilterTable tab is

=SUMPRODUCT(NOT(ISERROR(SEARCH(Choice,A5)))+0)>0

where Choice=CHOOSE(MATCH(AdvFilterTable!$A$2,CritLists,0),PowerBIList,VBAList,ExcelList)

This is the key formula for returning the desired list into the advanced filter formula.It is controlled by cell A2, which uses Data Validation with the following list.

CritLists=LList!$E$2:$E$4

The criteria lists tied to the CHOOSE function are are shown in the following figure.

 

These are expanding dynamic lists to add additional criteria if necessary.

PowerBIList=OFFSET(LList!$A$1,1,,COUNTA(LList!$A:$A)-1)

VBAList =OFFSET(LList!$B$1,1,,COUNTA(LList!$B:$B)-1)

ExcelList =OFFSET(LList!$C$1,1,,COUNTA(LList!$C:$C)-1)

When a list is selected in cell A2 and the advanced filter is applied to the links table, the result is as shown below.

 

Functionally, you can search all of my links for the content you desire. Also, you can use this filter model with your own data and criteria. I hope that this is useful.

You can download the file here.

ESL1-60_Filter