QUERY SELECT * WHERE contains in Google Sheets (2026)

Here's the baseline syntax:

=QUERY(data, "SELECT * WHERE ColLetter contains 'string'", headers)

Against a GL export sitting on a separate tab:

=QUERY('GL Export'!A:H, "SELECT * WHERE C contains 'Marketing'", 1)

That pulls every row where column C (account name, cost center, description - whatever's in C) includes "Marketing". All 8 columns come through in original order. The 1 at the end tells QUERY to treat row 1 as a header.

Column References in QUERY

QUERY uses actual spreadsheet column letters, not positional notation. If your data is 'GL Export'!A:H, query columns are A through H. If the range starts at column C ('GL Export'!C:J), you still reference C, D, E in the query string.

This bites people who've used SQL tools with zero-based or positional indexing. Sheets doesn't work that way.

Dynamic contains: Injecting a Cell Reference

Hard-coding a string inside QUERY is fine for one-off analysis. For a model that runs monthly, you want a dropdown on your Assumptions tab to drive the filter. The pattern is string concatenation outside the query:

=QUERY('GL Export'!A:H, 
  "SELECT * WHERE C contains '"&Assumptions!$B$2&"'", 1)

If Assumptions!$B$2 holds "Payroll", the effective query becomes SELECT * WHERE C contains 'Payroll' at runtime. Change the cell, the table updates. The single quotes wrapping the cell reference ('"&...&"') are required - missing them throws a parse error.

For a bank syndicate DCF where you're pulling from a 4,200-row cost detail tab:

=QUERY('Cost Detail'!A:J, 
  "SELECT A, B, D, G, J WHERE E contains '"&'Model Assumptions'!$C$4&"' 
   AND G > 0 
   ORDER BY G DESC", 1)

This filters to the cost category in your assumptions cell, excludes zero-amount rows, and sorts by amount - the kind of filtered summary view that goes directly into a lender presentation.

Multi-Condition WHERE with contains

You can chain conditions with AND/OR:

=QUERY('P&L'!A:F, 
  "SELECT * WHERE D contains 'Direct' AND E > 50000", 1)

This pulls every P&L row where the account description includes "Direct" and the period amount in column E exceeds $50,000. Useful for isolating direct cost lines above a materiality threshold when your P&L tab feeds a board pack summary.

The data type rule is non-negotiable: contains only works on text columns. Column E above uses > because it's numeric. Writing E contains '50000' returns a type mismatch error.

If your cost centers are labeled "Revenue - North", "Revenue - South", "Revenue - Online", one contains 'Revenue' catches all three without an OR chain. That's the practical advantage over exact-match filtering.

contains vs LIKE vs FILTER+SEARCH

Three approaches do substring matching in Sheets. They're not interchangeable:

The like case-sensitivity is the most common gotcha. According to Google's Sheets API documentation, contains performs a case-insensitive match while like respects case. "Marketing" and "marketing" are the same to contains. They're different to like. If you're filtering on user-typed values from a dropdown, contains is almost always the right choice.

The FILTER equivalent of a GL search:

=FILTER('GL Export'!A:H, 
  ISNUMBER(SEARCH(Assumptions!$B$2, 'GL Export'!C:C)))

FILTER references the cell directly without concatenation. QUERY wins when you also need ORDER BY, column selection, or aggregation alongside the filter. If you just need the rows, FILTER is less syntax.

A Practical Board Pack Example

Quarterly close, contribution margin report by segment. Your GL export has 3,800 rows across 9 cost centers. You want to surface only "Revenue" account lines into your executive summary tab, sorted by period amount:

=QUERY('GL Export'!A:I, 
  "SELECT A, B, C, F, I 
   WHERE D contains 'Revenue' 
   ORDER BY F DESC", 1)

This returns account date, entity, description, period amount, and YTD - 5 columns from 9 - filtered to revenue rows, sorted high-to-low. The columns your treasury team added for bank reporting disappear from the output without touching the source data. On a standard quarterly GL export this runs in under a second against 50,000 rows.

For a sensitivity on gross margin contribution at $4.2M revenue and 38.5% gross margin target, you'd point the same QUERY at a scenario output tab:

=QUERY('Scenario Output'!A:K, 
  "SELECT A, B, D, H WHERE C contains '"&'Sensitivity'!$B$3&"' 
   AND H >= "&'Sensitivity'!$C$3&" 
   ORDER BY H DESC", 1)

The mix of contains for the text filter and >= for the numeric threshold is the pattern you'll use in most real models.

What contains Won't Do

Numbers stored as numbers: WHERE F contains '4200000' fails. Use WHERE F = 4200000 or WHERE F >= 4000000. If you genuinely need substring matching on formatted numbers (say, matching "42" inside "1,420,000"), you need TEXT() to convert first, which means a helper column.

Leading/trailing spaces: contains 'Marketing ' (trailing space) won't match "Marketing" (without one). If you're pulling from an ERP export or HubSpot integration, trim your source data before querying. A helper column with =TRIM(C2) or an ARRAYFORMULA equivalent solves it.

Multi-column OR across different types: WHERE (B contains 'Revenue' OR D contains 'Revenue') works fine when both are text. It gets messy when B is text and D is numeric. Split into two QUERYs and combine with VSTACK if you're on a 2022+ Sheets version.

As of July 2026, Google Sheets caps at 10 million cells per spreadsheet. QUERY with contains on large GL exports (100,000+ rows across multiple tabs) can approach that limit fast. If you're hitting performance issues, that's a signal to move the heavy lifting out of Sheets formulas entirely.

If you're working with large external datasets - PostgreSQL cost tables, BigQuery financial data - ModelMonkey lets you query them from directly inside Sheets without formula gymnastics. The QUERY syntax above handles in-sheet data well; external data at scale is a different problem.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.