Concatenating arguments in the Filter() function

[adame145]

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 it still works

=filter(RangeToFilter,Sheet1!c3:c20=t2,"")

It also still works if I change the conditional value to a Named Range

=filter(RangeToFilter,Sheet1!c3:c20=ConditionValue,"")

When I change 'Sheet1!c3:c20' to a cell reference containing 'Sheet1!c3:c20' it still works

=filter(RangeToFilter,indirect(vlookup(a lookup to a 2-column range with column ranges listed)=ConditionValue,"")

Where I run into a problem is when I try to use concatenation (the '&' operator) to bring in the conditional operator

=filter(RangeToFilter,indirect(vlookup(a lookup to a 2-column range with column ranges listed)&ConditionOperator&ConditionValue,"")

Is there some way to use the Filter() function so it can construct its arguments by picking up the filtering condition from cells where my user will have selected his/her conditions?

[#2]

@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

[#3]

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

[#4 · Accepted solution]

@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

[#5]

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