How to Use the FILTER, ISNUMBER, and SEARCH Functions in Google Sheets for Case-Insensitive Keyword Searching

Building interactive search dashboards in Google Sheets is essential when managing customer directories, inventory databases, or ticket logs. While standard spreadsheet filters and Ctrl+F tools work for manual inspections, they cannot dynamically isolate records into clean summary reports based on user input cells. While the native FILTER function is powerful, standard equality checks (A2:A = "Smith") require exact matches and fail when users enter partial phrases or inconsistent casing. By combining FILTER, ISNUMBER, and SEARCH, you can construct a resilient, case-insensitive search engine that extracts matching rows dynamically as users type keywords.

Why Combine FILTER, ISNUMBER, and SEARCH?

Each function provides a critical mathematical component in the filtering chain:

• SEARCH(find_text, text_to_search): Searches for a substring within text. Unlike FIND (which is strictly case-sensitive), SEARCH is completely case-insensitive. If found, it returns the numeric starting position of the substring (e.g. 1, 5); if not found, it returns an #VALUE! error.
• ISNUMBER(value): Evaluates the output of SEARCH. If a match was found (a number), it converts it to TRUE. If no match was found (the error), it converts it safely to FALSE without crashing.
• FILTER(range, condition1, ...): Takes the array of TRUE and FALSE values generated by ISNUMBER and extracts all qualifying rows from the source table.

Basic Formula Syntax: Single Column Keyword Search

Suppose you have an employee directory in cells A2:C20 with columns Name (A), Department (B), and Email (C). You have a search input cell at E1 where users type queries (e.g. smith or JOHN).

To extract all matching employees, enter the following formula in cell E3:

=FILTER(A2:C20, ISNUMBER(SEARCH(E1, A2:A20)))

When cell E1 contains john, the formula returns rows for Johnathan Smith, Elton John, and John Doe simultaneously, regardless of whether characters are upper- or lower-case.

Handling Empty Search Inputs Gracefully

If the user clears the search cell E1, standard SEARCH interprets an empty cell as matching everything, which can flood the sheet, or trigger a #N/A error if no rows qualify. To handle blank inputs cleanly, wrap the formula inside an IF condition:

=IF(ISBLANK(E1), "Please enter a search keyword above", IFERROR(FILTER(A2:C20, ISNUMBER(SEARCH(E1, A2:A20))), "No matching records found"))

This ensures the dashboard displays intuitive user guidance rather than cryptic formula error codes.

Multi-Column Searching (OR Logic Across Fields)

In real-world applications, users often want a universal search bar that checks across multiple fields simultaneously—matching either by employee Name (Col A) OR Department (Col B) OR Email (Col C).

In boolean spreadsheet math, the addition operator (+) functions as an OR gate:

=FILTER(
  A2:C20,
  ISNUMBER(SEARCH(E1, A2:A20)) +
  ISNUMBER(SEARCH(E1, B2:B20)) +
  ISNUMBER(SEARCH(E1, C2:C20))
)

If the keyword finance is typed into E1, Google Sheets checks all three columns. If the term appears anywhere in that row, the entire record is returned.

Multi-Condition Keyword Searching (AND Logic)

If your dashboard includes separate search filters—for instance, searching by partial Name in cell E1 AND filtering by specific Department in cell F1—use the multiplication operator (*) for AND logic:

=FILTER(
  A2:C20,
  ISNUMBER(SEARCH(E1, A2:A20)) *
  ISNUMBER(SEARCH(F1, B2:B20))
)

Only rows that satisfy both criteria simultaneously are returned in the output array.

Performance Comparison Against REGEXMATCH

Technique	Formula Example	Pros & Cons
ISNUMBER + SEARCH	=FILTER(A2:C, ISNUMBER(SEARCH(E1, A2:A)))	Immune to special character crashes (brackets, plus signs, asterisks in user input do not break the formula). Highly recommended for user-facing search boxes.
REGEXMATCH	=FILTER(A2:C, REGEXMATCH(A2:A, "(?i)" & E1))	Powerful regex capabilities, but crashes with #ERROR! if users type raw punctuation or regex symbols like ( or [.

Using ISNUMBER(SEARCH(...)) provides the most robust, crash-proof architecture for building interactive, case-insensitive search interfaces in Google Sheets.