Wildcard in Drop-down menu used in Array Formula

Question

I have been searching the internet for hours! I have been working on an array index formula to search for multiple criteria and return multiple matches based on criteria in drop-down menu's. So, I have the following formula in cell E9 which searches cells E2,F2,G2,H2,I2 & J2 in the 'Summary' worksheet against their corresponding columns on the 'Main' worksheet.

=IFERROR(INDEX(Main!$E$3:$E$200,SMALL(IF(1=((--($E$2=Main!$A$2:$A$200))*(--($F$2<=Main!$C$2:$C$200))*(--($G$2>=Main!$D$2:$D$200))*(--($H$2=Main!$B$2:$B$200))*(--($I$2=Main!$H$2:$H$200))*(--($J$2=Main!$I$2:$I$200))),ROW(Main!$E$3:$E$200)-3,""),ROW()-8)),"")

Other formulas I have for SUMIF & COUNTIF allow the "*" to be selected in any of the drop-down lists to bypass that list, or 'select-all'. Is it possible to add something to the array formula to bypass that criterion if I want to 'select all'?

For example, in the images I've added Example 1 shows the Summary tab and Example 2 shows the Main tab. So, for the criteria I've chosen, what i want is to see a list in E9 downwards of all the journeys that 'Bob' has been on.

I would appreciate any advice.

Thanks very much.

Example 1

Example 2

Scott Craner Scott Craner · Accepted Answer · 2019-02-25T17:39:38

You will need to put IFs inside for each criteria:

If($E$2 = "Select All",1,$E$2=Main!$A$2:$A$200)

so:

=IFERROR(INDEX(Main!$E$2:$E$200,
  SMALL(
    IF(If($E$2 = "Select All",1,$E$2=Main!$A$2:$A$200)*
       If($F$2 = "Select All",1,$F$2<=Main!$C$2:$C$200)*
       If($G$2 = "Select All",1,$G$2>=Main!$D$2:$D$200)*
       If($H$2 = "Select All",1,$H$2=Main!$B$2:$B$200)*
       If($I$2 = "Select All",1,$I$2=Main!$H$2:$H$200)*
       If($J$2 = "Select All",1,$J$2=Main!$I$2:$I$200)
       ,ROW(Main!$E$2:$E$200)-1,"")
    ,ROW()-8)
  ),"")

Wildcard in Drop-down menu used in Array Formula

1 Answers