This may sound silly a first but I want to know if there is the way using Filter() to select ALL the values in a column.



I have a entry form on a WS made up of 4 hierarchical drop-down list (using data validation) which allow the user to filter a large table to produce extract only the rows corresponding to the crteria selected. I would like to allow the user to leave any of the last two criteria unselected to produce a less focused report

When a box is left blank, Filter() returns no matches, as there are no values in the table equal to "", ie:

= FILTER(Table;(Table[Column_A]=ValueA)*(Table[Column_B]=ValueB)*(Table[Column_C]="")*(Table[Column_D]=ValueD)

where the cell "ValueC" has been left blank.

There are work arounds (the one I used is a massive IFS() formula with separate filtering formulas for each of the possibility [it is nearly 1000 character long] making it difficult to maintain if changes are needed.

Is there a simpler way of doing it? I know that wilcard are not implemented for the filter() function.

I hope I was clear. Thank you

D. Gascon