SOLVED

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

Copper 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
best response confirmed by Muper Sega (Copper 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

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.

 

@Muper Sega 

 

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

 

Regards

1 best response

Accepted Solutions
best response confirmed by Muper Sega (Copper 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

View solution in original post