In Excel, filtering a dynamic array is simple. Something like this:
=FILTER(my_array,IF(condition,filter1,filter2))
But what do I use if filter2 (say) is simply the TRUE condition; i.e. if condition is false I just want to reproduce my_array without any filtering.
This doesn’t work:
=FILTER(my_array,IF(condition,filter1,TRUE))
presumably because the filters need to be arrays of the same X or Y dimension as my_array, and TRUE is scalar.
At the moment I’m forced to put the FILTER() inside the IF() instead of vice versa, as in:
=IF(condition,FILTER(my_array,filter1),my_array)
But when the description of my_array is long and complex — a big HSTACK() for example, with lots of INDEX() and MATCH() and CHOOSECOLS() etc statements — writing it out twice is unpleasant.
So how’s it done?
