# How to use QUERY contains in Google Sheets

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

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |

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

Result (spills 5 × 3): Rep, Product, Revenue; Ana Ruiz, Widget, $2,080; Kim Ortiz, Widget, $2,860; Raj Patel, Widget Pro, $1,422; Raj Patel, Widget, $1,040

## 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.

## Other ways

- `=QUERY(A1:F13,"select B, D, F where lower(D) contains 'widget'",1)`: lower() makes the test ignore case.
- `=QUERY(A1:F13,"select B, D, F where D like 'Widget%'",1)`: like 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)`: Put 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.

## 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).*'`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/query-contains-text