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