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

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


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


If you know what I mean...


Does anyone have any ideas


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

@Muper Sega 




I presume you meant




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




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:




can be abbreviated to, for example




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



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.