Spreadsheet Practice

How to use QUERY contains in Google Sheets

Google Sheets formula

=QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1)

Use contains in the where clause: =QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1) matches Widget and Widget Pro.

Result on the example
a 5-row list that spills from H1 in H1
Excel
See below

See it work

The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.

How it works

contains is TRUE when the text appears anywhere in the value, so "Widget Pro" passes as well as "Widget". It is case-sensitive, like every text test in QUERY. QUERY also has starts with, ends with, like and matches for other kinds of partial match.

=QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1)

Result{5 rows × 3 cols} "Rep", "Product", "Revenue", "Ana Ruiz", …

  1. A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …

    A range: 13 rows × 6 columns.

  2. QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1){5 rows × 3 cols} "Rep", "Product", "Revenue", "Ana Ruiz", …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
    • query"select B, D, F where D contains 'Widget'"
    • headers1

In Excel

Excel has no QUERY function. Use FILTER with SEARCH: =FILTER(A2:F13,ISNUMBER(SEARCH("Widget",D2:D13))).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=QUERY(A1:F13,"select B, D, F where lower(D) contains 'widget'",1)spills 5 × 3lower() makes the test ignore case.
=QUERY(A1:F13,"select B, D, F where D like 'Widget%'",1)spills 5 × 3like uses % for any run of characters and _ for one character. Here: names that start with Widget.
=QUERY(A1:F13,"select B, D, F where not D contains 'Widget'",1)spills 9 × 3Different resultPut not in front to exclude the matches. Every order without Widget in the product name.

Common mistakes

  • contains is case-sensitive: contains 'widget' finds nothing here. Use lower(D) contains 'widget'.
  • Wildcards like * don't work with contains. Use like with %, or matches with a regular expression.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use QUERY contains

In H1, use QUERY to return the Rep, Product and Revenue of every order whose product name contains Widget.

Questions

How do I search for text typed in a cell?

Join it in: "where D contains '"&I1&"'".

How do I match any of several words?

Use matches with a regular expression: where D matches '.*(Widget|Gizmo).*'.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.