Today I needed to build a dynamic filter in Google Sheets using the QUERY function so I can enter criteria in separate cells (country, sector, etc.) and have the results update automatically. I wanted two core features:

  1. Optional filters that only apply when the criterion cell is nonempty.

  2. The ability to specify multiple allowed values for a column (e.g. country in (‘US', ‘CA', ‘MX')) dynamically.

First, I set up a Data tab whose columns map to my fields:

  • A: country
  • B: sector
  • C: marketcap_class
  • …and so on.

On my “Controls” sheet, I reserved cells like A2 for country, B2 for sector, C2 for marketcap_class, and a small vertical range F2:F4 for multiple country codes.

  1. Basic QUERY with required filters
=QUERY(
  Data!A1:E,
  "select A, B, C, D, E
   where A = '" & $A$2 & "'
     and B = '" & $B$2 & "'",
  1
)

That works if every filter cell is filled.

  1. Making filters optional
    I wrapped each filter clause in an IF that checks if the cell is nonblank:
=QUERY(
  Data!A1:E,
  "select A, B, C, D, E where 1=1"
    & IF($A$2<>"", " and A = '" & $A$2 & "'", "")
    & IF($B$2<>"", " and B = '" & $B$2 & "'", "")
    & IF($C$2<>"", " and C = '" & $C$2 & "'", ""),
  1
)

The where 1=1 is a no‑op that simplifies adding “and …” clauses conditionally.

  1. Supporting multiple options with IN
    To allow several country codes, I used an IN clause and concatenated the values from cells F2:F4:
=QUERY(
  Data!A1:E,
  "select A, B, C, D, E
   where 1=1"
    & IF(COUNTA($F$2:$F$4),
         " and A in ('"
           & JOIN("','", FILTER($F$2:$F$4, $F$2:$F$4<>""))
           & "')",
         ""
       )
    & ...other optional clauses...,
  1
)

Here, FILTER($F$2:$F$4, $F$2:$F$4<>"") picks only nonblank cells, and JOIN builds the comma‑separated quoted list. If no cells are filled, the IN clause is skipped.

Key takeaways:

  • Use where 1=1 so you can prepend every condition with and … without worrying about syntax.
  • Wrap clauses in IF(cell<>"", clause, "") to make filters optional.
  • Build dynamic IN lists with FILTER and JOIN to handle multiple selections.