SOLVED

New Contributor

# 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

3 Replies
best response confirmed by Muper Sega (New Contributor)
Solution

# Re: 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

# Re: Save me from my mess. Dynamic Filter by array values.

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.

# Re: Save me from my mess. Dynamic Filter by array values.

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

Regards