How many search strings can you add to one filter?

Frank Bons 20 Reputation points
2026-10-19T14:08:16.9166667+00:00

How many search strings can you add to one filter?

It seems there is a limit, for the filter doesn't change any more after these search strings:

=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)))))))));""))

Or is there a way to search with more strings?

Thanks in advance,

Frank

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments

Answer accepted by question author

AI answer

2026-10-19T14:09:10.6266667+00:00

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:

  • Plaats must occur in column A;
  • Trip must occur in column D;
  • Multimodaal must occur in column E;
  • Direction must 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

  1. SEARCH function | Microsoft Support
  2. IS functions | Microsoft Support
  3. FILTER function | Microsoft Support
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.