SOLVED

Save me from my mess. Dynamic Filter by array values.

%3CLINGO-SUB%20id%3D%22lingo-sub-1423169%22%20slang%3D%22en-US%22%3ESave%20me%20from%20my%20mess.%20Dynamic%20Filter%20by%20array%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423169%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20loving%20the%20dynamic%20array%20functions%20but%20I%20have%20gotten%20to%20a%20point%20where%20my%20hacky%20solutions%20just%20won't%20do.%20The%20mess%20image%20is%20an%20example%20of%20why%20I%20need%20a%20better%20solution.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22big%20mess.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194981i6E4C99959604DD36%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22big%20mess.png%22%20alt%3D%22big%20mess.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20filtering%20by%20multiple%20conditions%20for%20example%3C%2FP%3E%3CP%3E%3Dfilter(array2%2C(array2%3Da1)*(array2%3Da2)*(array2%3Da3)*(array2%3Da4))%3C%2FP%3E%3CP%3Enow%20I'm%20sure%20you%20understand%20that%20it%20would%20be%20much%20nicer%20to%20say%20something%20like%3C%2FP%3E%3CP%3E%3Dfilter(array2%2Carray2%3Dor(a1%2Ca2%2Ca3%2Ca4)%3C%2FP%3E%3CP%3EIf%20you%20know%20what%20I%20mean...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20ideas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1423169%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-1423423%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20me%20from%20my%20mess.%20Dynamic%20Filter%20by%20array%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F129904%22%20target%3D%22_blank%22%3E%40Muper%20Sega%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%3EI%20presume%20you%20meant%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DFILTER(array2%2C(array2%3DA1)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA2)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA3)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA4))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20with%20%3CSTRONG%3Eaddition%3C%2FSTRONG%3E%20of%20the%20criteria%20arrays%2C%20and%20not%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DFILTER(array2%2C(array2%3DA1)%3CFONT%20color%3D%22%23FF0000%22%3E*%3C%2FFONT%3E(array2%3DA2)%3CFONT%20color%3D%22%23FF0000%22%3E*%3C%2FFONT%3E(array2%3DA3)%3CFONT%20color%3D%22%23FF0000%22%3E*%3C%2FFONT%3E(array2%3DA4))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewith%20%3CSTRONG%3Emultiplication%3C%2FSTRONG%3E%20of%20the%20criteria%20arrays%2C%20as%20you%20give%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20latter%20is%20simulating%20an%20AND%20condition%2C%20the%20former%20an%20OR%20condition.%20Since%20a%20value%20in%20%3CEM%3Earray2%20%3C%2FEM%3Ecannot%20%3CEM%3Esimultaneously%3C%2FEM%3E%20be%20equal%20to%20each%20of%20the%20four%20values%20in%20A1%3AA4%20(unless%20of%20course%20those%20four%20values%20are%20the%20same)%2C%20I%20would%20imagine%20that%20your%20formula%20would%20produce%20a%26nbsp%3B%23CALC!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20to%20your%20query%20re%20simplification%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000080%22%3E%3DFILTER(array2%2C(array2%3DA1)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA2)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA3)%3CFONT%20color%3D%22%23FF0000%22%3E%2B%3C%2FFONT%3E(array2%3DA4))%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20be%20abbreviated%20to%2C%20for%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DFILTER(array2%2CISNUMBER(MATCH(array2%2CA1%3AA4%2C0)))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuggest%20you%20upload%20an%20actual%20workbook%20if%20that%20doesn't%20resolve%20your%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ERegards%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423488%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20me%20from%20my%20mess.%20Dynamic%20Filter%20by%20array%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423488%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20beautiful%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%20%2C%20correct%20I%20am%20running%20the%20or%20not%20the%20and%20in%20my%20current%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20a%20charm%20Jos%2C%20I%20have%20no%20idea%20why%20but%20I%20shall%20do%20more%20analysis%20to%20check%20it%20out.%20Thank%20you%20so%20much%20for%20the%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423741%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20me%20from%20my%20mess.%20Dynamic%20Filter%20by%20array%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423741%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F129904%22%20target%3D%22_blank%22%3E%40Muper%20Sega%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20feedback!%20Glad%20to%20hear%20it's%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I have been loving the dynamic array functions but I have gotten to a point where my hacky solutions just won't do. The mess image is an example of why I need a better solution.

big mess.png

 

I am currently filtering by multiple conditions for example

=filter(array2,(array2=a1)*(array2=a2)*(array2=a3)*(array2=a4))

now I'm sure you understand that it would be much nicer to say something like

=filter(array2,array2=or(a1,a2,a3,a4)

If you know what I mean...

 

Does anyone have any ideas

 

3 Replies
Highlighted
Best Response confirmed by Muper Sega (New Contributor)
Solution

@Muper Sega 

 

Hi,

 

I presume you meant

 

=FILTER(array2,(array2=A1)+(array2=A2)+(array2=A3)+(array2=A4))

 

i.e. with addition of the criteria arrays, and not

 

=FILTER(array2,(array2=A1)*(array2=A2)*(array2=A3)*(array2=A4))

 

with multiplication of the criteria arrays, as you give?

 

The latter is simulating an AND condition, the former an OR condition. Since a value in array2 cannot simultaneously be equal to each of the four values in A1:A4 (unless of course those four values are the same), I would imagine that your formula would produce a #CALC! error.

 

As to your query re simplification:

 

=FILTER(array2,(array2=A1)+(array2=A2)+(array2=A3)+(array2=A4))

 

can be abbreviated to, for example

 

=FILTER(array2,ISNUMBER(MATCH(array2,A1:A4,0)))

 

Suggest you upload an actual workbook if that doesn't resolve your issue.

 

Regards

Highlighted

This is beautiful @Jos_Woolley , correct I am running the or not the and in my current one.

 

It works a charm Jos, I have no idea why but I shall do more analysis to check it out. Thank you so much for the assist.

 

Highlighted

@Muper Sega 

 

Thanks for the feedback! Glad to hear it's working.

 

Regards