A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
There is no indication that four search strings exceed a FILTER limit; the formula stops filtering correctly because the second, third, and fourth conditions are nested inside one another instead of being evaluated independently.
Use this corrected formula:
=ALS(Plaats="";"";ALS.FOUT(
FILTER(A4:O110;
ISGETAL(VIND.SPEC(Plaats;A4:A110))*
ISGETAL(VIND.SPEC(Trip;D4:D110))*
ISGETAL(VIND.SPEC(Multimodaal;E4:E110))*
ISGETAL(VIND.SPEC(Direction;F4:F110));
"");
""))
Each VIND.SPEC search returns the numeric position when text is found and an error when it is not. Each result must therefore have its own ISGETAL, converting it independently into TRUE or FALSE.^1^ ^2^
The multiplication signs then mean AND:
-
Plaatsmust occur in column A; -
Tripmust occur in column D; -
Multimodaalmust occur in column E; -
Directionmust occur in column F.
Microsoft documents this multiplication pattern for applying multiple AND conditions in FILTER.^3^
Why the original formula stops changing
In the original formula, expressions resembling this occur:
ISGETAL(VIND.SPEC(Trip;D4:D110) * volgende_voorwaarde)
If volgende_voorwaarde is FALSE, multiplication can produce 0. Because zero is still a number, ISGETAL(0) returns TRUE. Consequently, adding another condition can fail to exclude rows as intended.
If blank search cells should be ignored
If any of the four named search cells may be blank and a blank should mean “do not filter this column,” use:
=ALS.FOUT(FILTER(A4:O110;
ALS(Plaats="";1;ISGETAL(VIND.SPEC(Plaats;A4:A110)))*
ALS(Trip="";1;ISGETAL(VIND.SPEC(Trip;D4:D110)))*
ALS(Multimodaal="";1;ISGETAL(VIND.SPEC(Multimodaal;E4:E110)))*
ALS(Direction="";1;ISGETAL(VIND.SPEC(Direction;F4:F110)));
"");"")
You can extend the same pattern with additional criteria:
*ALS(NieuwZoekveld="";1;
ISGETAL(VIND.SPEC(NieuwZoekveld;bijbehorend_bereik)))
Keep every criterion range the same height as A4:O110, because the FILTER include-array must have the same height or width as the range being filtered.^3^
References