Forum Discussion
Save me from my mess. Dynamic Filter by array values.
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.
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
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
3 Replies
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
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.
