If you have a massive dataset in Google Sheets, searching for and extracting specific information can be incredibly tedious. While VLOOKUP and XLOOKUP are excellent for finding single rows of data, they fall short when you need to extract and filter entire tables based on complex criteria. For this, Google Sheets offers a unique and incredibly powerful function: QUERY.
The QUERY function allows you to use a pseudo-SQL (Structured Query Language) syntax directly within your spreadsheet. In this guide, you will learn how to use the QUERY function to filter, sort, and extract data with a single formula.
Understanding the QUERY Syntax
The basic structure of the function looks like this:
=QUERY(data, query, [headers])- data: The range of cells you want to search through (e.g., A1:F100).
- query: The actual SQL-like command, enclosed in quotation marks (e.g., “SELECT A, B WHERE C = ‘Yes'”).
- headers (optional): The number of header rows at the top of your data. (Usually set to 1).
Scenario Setup
Imagine you have a sales dataset from A1 to E100. The columns are:
- A: Date
- B: Salesperson Name
- C: Region (e.g., North, South, East, West)
- D: Product
- E: Revenue
1. The Basic SELECT Command
The simplest use of QUERY is to duplicate specific columns from your dataset. If you only want to see the Salesperson Name (Column B) and Revenue (Column E), type this into an empty cell:
=QUERY(A1:E100, "SELECT B, E", 1)Google Sheets will automatically populate the cells below and to the right of your formula with the requested columns.
2. Filtering Data with the WHERE Clause
The true power of QUERY comes from filtering. Suppose you only want to extract sales data for the “North” region.
=QUERY(A1:E100, "SELECT B, D, E WHERE C = 'North'", 1)Important Syntax Rule: Text strings inside the query must be wrapped in single quotes (‘North’). If you are querying numbers, do not use quotes (e.g., WHERE E > 1000).
3. Combining Multiple Conditions (AND / OR)
You can make your filters as complex as you need. To find sales in the North region that generated over $500 in revenue:
=QUERY(A1:E100, "SELECT B, D, E WHERE C = 'North' AND E > 500", 1)To find sales from either the North or South region:
=QUERY(A1:E100, "SELECT B, D, E WHERE C = 'North' OR C = 'South'", 1)4. Sorting the Extracted Data (ORDER BY)
Instead of manually sorting your extracted data, you can instruct the QUERY function to do it for you. To extract the North region sales and sort them from highest revenue to lowest (descending order):
=QUERY(A1:E100, "SELECT B, E WHERE C = 'North' ORDER BY E DESC", 1)Use ASC instead of DESC for ascending order.
5. Using Mathematical Operations and Grouping
You can even use QUERY to perform calculations on the fly, similar to a Pivot Table. If you want to see the total revenue generated by each individual salesperson, you can group the data by Column B (Salesperson) and sum Column E (Revenue):
=QUERY(A1:E100, "SELECT B, SUM(E) GROUP BY B", 1)Troubleshooting Common Errors
- #VALUE!: The most common cause is a syntax error in your query string. Ensure you have used double quotes to wrap the entire query, and single quotes around specific text criteria.
- Case Sensitivity: The query language is case-sensitive.
SELECTandWHEREshould ideally be capitalised, and your column references (A, B, C) must be capitalised.select a, bwill result in an error. - #REF!: This happens if there is existing data in the cells where the QUERY function is trying to output its results. You must ensure there is enough empty space below and to the right of your formula.
By mastering the Google Sheets QUERY function, you can replace dozens of complex VLOOKUP, FILTER, and SORT formulas with a single, elegant command.