Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use QUERY with LIKE wildcards in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=FILTER(A2:C10,ISNUMBER(SEARCH("Pro",B2:B10)))Google Sheets
=QUERY(A1:C10,"SELECT A, B, C WHERE B LIKE '%Pro%'",1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the column you want to do a partial-match filter on — for example, B holds product names.
- 2To match rows where B contains the text 'Pro' anywhere: =QUERY(A1:C10,"SELECT A, B, C WHERE B LIKE '%Pro%'",1).
- 3To match rows where B starts with 'Pro': use WHERE B LIKE 'Pro%' (no leading percent).
- 4To match rows where B ends with 'Plus': use WHERE B LIKE '%Plus'.
- 5To inject a dynamic search term from cell E2, concatenate: "SELECT A, B, C WHERE B LIKE '%"&E2&"%'".
Tips
- LIKE matching in QUERY is case-sensitive. 'Pro' will not match 'pro'. To work around this, add a helper column with LOWER() applied to the search column, then use LIKE '%pro%' against that helper column.
- The underscore (_) is also a LIKE wildcard that matches exactly one character — WHERE B LIKE 'Item_' matches 'Item1', 'ItemA', etc., but not 'Item12'.
- Combining LIKE with AND/OR is valid: WHERE B LIKE '%Pro%' AND C > 500 filters on both partial text and a numeric threshold simultaneously.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does QUERY LIKE support regular expressions?
No. QUERY LIKE only supports the SQL-style % (any string) and _ (single character) wildcards. For regex matching, use REGEXMATCH inside a Google Sheets helper column and query that boolean column.
How do I do a case-insensitive LIKE search?
Add a helper column with =LOWER(B2) dragged down, then query LIKE '%pro%' on that column. Alternatively, use FILTER with SEARCH which is case-insensitive: =FILTER(A2:C10, ISNUMBER(SEARCH(E2, B2:B10))).
Can I use LIKE with a cell reference for the pattern?
Yes — concatenate: "WHERE B LIKE '%"&E2&"%'". If E2 contains 'Pro', the effective query string becomes WHERE B LIKE '%Pro%'.
More on QUERY (Google Sheets SQL)
See all →How-to
How to use QUERY to filter data
Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.
Error fixFix QUERY error
QUERY parse error in Google Sheets means the SQL-like query string has a syntax mistake. Find the exact error and fix it here.
How-toHow to use QUERY to sum by group
Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.
How-toHow to use QUERY to group by multiple columns
Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.
How-toHow to use QUERY with WHERE, AND, OR
Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.
How-toHow to create a running total with QUERY
Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.