How to Use the LAMBDA and FILTER Functions in Google Sheets for Multi-Condition Dynamic Search

Building interactive search dashboards in Google Sheets often presents a classic formula challenge: when users leave search criteria cells blank, traditional FILTER functions treat the empty cell as a literal search term, returning zero records or #N/A errors. Resolving this traditionally required nesting deeply convoluted IF statements or writing bulky QUERY strings with fragile string concatenations. By encapsulating FILTER inside a custom LAMBDA formula, you can build clean, self-documenting multi-condition search engines that gracefully handle optional search inputs.

The Challenge of Optional Criteria in Standard FILTER

Consider a standard formula filtering sales records in range A2:D100 based on Department in cell F1 and Minimum Revenue in cell F2:

=FILTER(A2:D100, A2:A100 = F1, D2:D100 >= F2)

If the user leaves F1 empty (hoping to see all departments), the formula searches for blank department cells and returns nothing. To make criteria optional, each condition must verify whether the input cell is blank:

=FILTER(A2:D100, (F1 = "") + (A2:A100 = F1), (F2 = "") + (D2:D100 >= F2))

While effective, repeating these expressions across multiple search fields creates long, error-prone formulas. LAMBDA solves this by abstracting the logic into clean variable definitions.

Building a Reusable Search Formula with LAMBDA and LET

Combining LET with LAMBDA allows you to assign readable variable names to search inputs and define modular filtering rules in a single cell:

=LET(
  data, A2:D100,
  deptCol, INDEX(data, , 1),
  revCol, INDEX(data, , 4),
  deptInput, F1,
  minRevInput, F2,
  FILTER(
    data,
    IF(deptInput = "", SEQUENCE(ROWS(data), 1, 1, 0), deptCol = deptInput),
    IF(minRevInput = "", SEQUENCE(ROWS(data), 1, 1, 0), revCol >= minRevInput)
  )
)

Handling Partial Text Matches with SEARCH and ISNUMBER

For text search inputs where users type partial keywords (e.g., typing “soft” to find “Software”), integrate SEARCH and ISNUMBER inside the lambda definition:

=LET(
  data, A2:D100,
  clientCol, INDEX(data, , 2),
  query, F1,
  matchCondition, IF(query = "", TRUE, ISNUMBER(SEARCH(query, clientCol))),
  FILTER(data, matchCondition)
)

Creating a Named Function for Company-Wide Use

To enable colleagues to use your search calculation across any spreadsheet without copying complex formulas, save it as a Named Function:

1. Click Data > Named functions.
2. Click Add new function.
3. Name the function: DYNAMIC_SEARCH.
4. Define function parameters: data_range, search_col, search_term.
5. In the Formula definition box, enter:

FILTER(data_range, IF(search_term = "", SEQUENCE(ROWS(data_range), 1, 1, 0), search_col = search_term))

6. Click Save.

Now, any team member can perform dynamic filtering with a clean, concise formula: =DYNAMIC_SEARCH(A2:D100, B2:B100, F1).

Filtering Logic Comparison

Approach	Blank Handling	Formula Complexity	Speed & Maintainability
Basic FILTER	Breaks (Returns 0 records)	Low	Poor for interactive forms
Boolean Multiplied FILTER	Functional	High (Verbose brackets)	Prone to syntax typos
LAMBDA / LET Abstracted	Handled gracefully	Modular and Clean	High readability and reusability

Pairing LAMBDA with FILTER elevates Google Sheets from a static tabular ledger into an interactive, enterprise-grade reporting application.