Forum Discussion

adame145's avatar
adame145
Copper Contributor
May 29, 2020
Solved

Concatenating arguments in the Filter() function

I've built a formula with filter() that works fine when I type in the range names for the arguments directly =filter(b3:r20,Sheet1!c3:c20=t2,"") And if I replace b3:r20 with the name of the range i...
excel
Formulas and Functions
  • Jos_Woolley's avatar
    Jos_Woolley
    May 29, 2020

    adame145 

     

    And that's precisely what I've provided you with. Have you not tested? Choose any cell you like to hold the operator, e.g. W1:

     

    =FILTER(RangeToFilter,COUNTIF(ConditionValue,W1&INDIRECT(VLOOKUP("Q",$T$4:$U$5,2,0))),"")

     

    This was not a legitimate possibility with your formula and the whole point of my switching to a construction involving COUNTIF.

     

    Regards

Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
May 29, 2020

adame145 

 

Hi,

 

=FILTER(RangeToFilter,COUNTIF(ConditionValue,">"&INDIRECT(VLOOKUP(your_v_lookup))),"")

 

Amend the condition (">" here) as required. Note that it needs to be this way round, i.e. the condition precedes the array being queried, so just be careful with your logic: effectively the above is filtering for values which are less than ConditionValue.

 

Regards

 

 

adame145's avatar
adame145
Copper Contributor
May 29, 2020
Hmmn... Thanks for your response - I'm not sure I get it. My problem is not the logic - I'm getting the filter results I want. My question is about how I can swap in different conditional operators without making the user edit the formula. I'm trying to give them a cell where they can pick the operator they want from a drop down and then the Filter formula get's constructed by concatenating their choice into the function's argument...
  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    May 29, 2020

    adame145 

     

    And that's precisely what I've provided you with. Have you not tested? Choose any cell you like to hold the operator, e.g. W1:

     

    =FILTER(RangeToFilter,COUNTIF(ConditionValue,W1&INDIRECT(VLOOKUP("Q",$T$4:$U$5,2,0))),"")

     

    This was not a legitimate possibility with your formula and the whole point of my switching to a construction involving COUNTIF.

     

    Regards

    • adame145's avatar
      adame145
      Copper Contributor
      May 29, 2020
      Fantastic - yes! I'm not sure why countif and reversing the argument order works (I'll have to think about that some more) but it does so thank you!!!

Resources