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 it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.