SOLVED

Concatenating arguments in the Filter() function

%3CLINGO-SUB%20id%3D%22lingo-sub-1426919%22%20slang%3D%22en-US%22%3EConcatenating%20arguments%20in%20the%20Filter()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426919%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20built%20a%20formula%20with%20filter()%20that%20works%20fine%20when%20I%20type%20in%20the%20range%20names%20for%20the%20arguments%20directly%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3Dfilter(b3%3Ar20%2CSheet1!c3%3Ac20%3Dt2%2C%22%22)%3C%2FP%3E%3CP%3EAnd%20if%20I%20replace%20b3%3Ar20%20with%20the%20name%20of%20the%20range%20it%20still%20works%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3Dfilter(%3CSTRONG%3ERangeToFilter%3C%2FSTRONG%3E%2CSheet1!c3%3Ac20%3Dt2%2C%22%22)%3C%2FP%3E%3CP%3EIt%20also%20still%20works%20if%20I%20change%20the%20conditional%20value%20to%20a%20Named%20Range%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3Dfilter(RangeToFilter%2CSheet1!c3%3Ac20%3D%3CSTRONG%3EConditionValue%3C%2FSTRONG%3E%2C%22%22)%3C%2FP%3E%3CP%3EWhen%20I%20change%20'Sheet1!c3%3Ac20'%20to%20a%20cell%20reference%20containing%26nbsp%3B'Sheet1!c3%3Ac20'%20it%20still%20works%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3Dfilter(RangeToFilter%2C%3CSTRONG%3Eindirect(vlookup(a%20lookup%20to%20a%202-column%20range%20with%20column%20ranges%20listed)%3C%2FSTRONG%3E%3DConditionValue%2C%22%22)%3C%2FP%3E%3CP%3EWhere%20I%20run%20into%20a%20problem%20is%20when%20I%20try%20to%20use%20concatenation%20(the%20'%26amp%3B'%20operator)%20to%20bring%20in%20the%20conditional%20operator%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3Dfilter(RangeToFilter%2Cindirect(vlookup(a%20lookup%20to%20a%202-column%20range%20with%20column%20ranges%20listed)%3CSTRONG%3E%26amp%3BConditionOperator%26amp%3B%3C%2FSTRONG%3EConditionValue%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20some%20way%20to%20use%20the%20Filter()%20function%20so%20it%20can%20construct%20its%20arguments%20by%20picking%20up%20the%20filtering%20condition%20from%20cells%20where%20my%20user%20will%20have%20selected%20his%2Fher%20conditions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1426919%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427035%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenating%20arguments%20in%20the%20Filter()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536226%22%20target%3D%22_blank%22%3E%40adame145%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DFILTER(RangeToFilter%2CCOUNTIF(ConditionValue%2C%22%26gt%3B%22%26amp%3BINDIRECT(VLOOKUP(%3CEM%3Eyour_v_lookup%3C%2FEM%3E)))%2C%22%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAmend%20the%20condition%20(%22%26gt%3B%22%20here)%20as%20required.%20Note%20that%20it%20needs%20to%20be%20this%20way%20round%2C%20i.e.%20the%20condition%20precedes%20the%20array%20being%20queried%2C%20so%20just%20be%20careful%20with%20your%20logic%3A%20effectively%20the%20above%20is%20filtering%20for%20values%20which%20are%20less%20than%26nbsp%3B%3CEM%3EConditionValue%3C%2FEM%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427086%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenating%20arguments%20in%20the%20Filter()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427086%22%20slang%3D%22en-US%22%3EHmmn...%20Thanks%20for%20your%20response%20-%20I'm%20not%20sure%20I%20get%20it.%20My%20problem%20is%20not%20the%20logic%20-%20I'm%20getting%20the%20filter%20results%20I%20want.%20My%20question%20is%20about%20how%20I%20can%20swap%20in%20different%20conditional%20operators%20without%20making%20the%20user%20edit%20the%20formula.%20I'm%20trying%20to%20give%20them%20a%20cell%20where%20they%20can%20pick%20the%20operator%20they%20want%20from%20a%20drop%20down%20and%20then%20the%20Filter%20formula%20get's%20constructed%20by%20concatenating%20their%20choice%20into%20the%20function's%20argument...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427343%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenating%20arguments%20in%20the%20Filter()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536226%22%20target%3D%22_blank%22%3E%40adame145%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20that's%20precisely%20what%20I've%20provided%20you%20with.%20Have%20you%20not%20tested%3F%20Choose%20any%20cell%20you%20like%20to%20hold%20the%20operator%2C%20e.g.%20W1%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DFILTER(RangeToFilter%2CCOUNTIF(ConditionValue%2CW1%26amp%3BINDIRECT(VLOOKUP(%22Q%22%2C%24T%244%3A%24U%245%2C2%2C0)))%2C%22%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20was%20not%20a%20legitimate%20possibility%20with%20your%20formula%20and%20the%20whole%20point%20of%20my%20switching%20to%20a%20construction%20involving%20COUNTIF.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427941%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenating%20arguments%20in%20the%20Filter()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427941%22%20slang%3D%22en-US%22%3EFantastic%20-%20yes!%20I'm%20not%20sure%20why%20countif%20and%20reversing%20the%20argument%20order%20works%20(I'll%20have%20to%20think%20about%20that%20some%20more)%20but%20it%20does%20so%20thank%20you!!!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

4 Replies
Highlighted

@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

 

 

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

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