If you’ve ever written SQL queries to get data out of a database, then you’ll recognize the QUERY function. If you don’t have database experience, the QUERY function is still very easy to learn.
What Is the QUERY Function?
The function has three main parameters: =QUERY(data, query, headers) These parameters are fairly straightforward.
Data: The range of cells that contain the source dataQuery: A search statement describing how to extract what you want from the source dataHeaders: An optional argument that lets you combine multiple headers in the source range into a single header in the destination sheet
The flexibility and power of the QUERY function comes from the Query argument, as you’ll see below.
How to Create a Simple QUERY Formula
The QUERY formula is especially useful when you have a very large data set from which you need to extract and filter data. The following examples use U.S. SAT high school performance statistics. In this first example, you’ll learn how to write a simple QUERY formula that returns all high schools and their data where “New York” is in the name of the school. Because the the Google Sheets QUERY function is very easy to understand and use, you can pull any data out of any large data set using simple query statements like the ones above. =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B LIKE ‘%New York%’”). This formula includes the following arguments:
Range of Cells: The range of data in A1 to F460 in Sheet1SELECT Statement: A SELECT statement that calls for any data in columns B, C, D, E, and F where column B contains text that has the word “New York” in it.
=Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”). Using the = operator finds an exact match and can be used to find matching text or numbers in any column.
Use the QUERY Function With a Comparison Operator
Comparison operators let you use the QUERY function to filter out data that doesn’t meet a condition. You have access to all of the following operators in a QUERY function:
=: Values match the search value<: Values are less than the search value>: Values are greater than the search value<=: Values are less than or equal to the search value>=: Values are greater than or equal to the search value<> and !=: Search value and source values are not equal
Using the same SAT example data set above, let’s take a look at how to see which schools had an average mathematics mean above 500 points.
Advanced Uses of QUERY Function
There are a few other features you can add to the QUERY function with some additional commands. These commands let you aggregate values, count values, order data, and find maximum values. =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE E > 500”) This formula calls for any data where column E contains a value that’s greater than 500. =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600”) =Query(Sheet1!A1:B24,“SELECT A, AVG(B) GROUP BY A”) =QUERY(Sheet1!A2:F460,“SELECT B, COUNT (F) GROUP BY B”) =QUERY(Sheet1!A2:F460,“SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)”)
