HomeGoogle DocsSpreadsheetUse QUERY Function as an Alternative to FILTER Function in Google Sheets

Use QUERY Function as an Alternative to FILTER Function in Google Sheets

Published on

I have recently leveraged the impressive QUERY feature to automatically populate information based on a drop-down selection. Upon inspection, you can observe how effortlessly we can extract essential information from a comprehensive database using QUERY.

Using the QUERY function as an alternative to the FILTER function in Google Sheets is quite straightforward.

You can utilize the query to filter data in seconds! I distinctly notice a few advantages when opting for the QUERY function over the FILTER Function.

Advantages of Using QUERY Function as an Alternative to FILTER Function

The QUERY function offers several advantages over the FILTER function, especially in scenarios that require more complex data manipulation and extraction:

Choosing Columns:

When employing the QUERY function to filter data, you have the flexibility to omit columns that you do not want to appear in your result. This proves particularly useful when using value expressions such as IMPORTRANGE for data. In contrast, using FILTER may require additional functions like CHOOSECOLS, INDEX, and ARRAY_CONSTRAIN to select the desired columns.

String Comparison Operators:

QUERY provides complex string comparison operators for advanced filtering. In the case of FILTER, additional functions like SEARCH, FIND, or REGEXMATCH may be necessary.

Case Sensitivity:

This is another feature of the QUERY function that proves beneficial in specific cases.

Sorting:

The QUERY function can filter and sort data in a single operation.

Field Labels:

Unlike FILTER, QUERY can identify the header row, ensuring it does not filter out the header row when specified. Additionally, you have the option to rename columns within the formula.

Aggregation:

Beyond filtering, the QUERY function can aggregate and pivot data.

Offset:

In addition to filtering, it can offset a specified number of rows.

While FILTER is more straightforward for basic filtering tasks, QUERY scores in situations where you need advanced querying capabilities and a greater degree of control over the output.

The choice between QUERY and FILTER depends on the complexity of your data analysis requirements.

Examples of Using QUERY Function as an Alternative to FILTER Function

Firstly, let me clarify one thing: it’s not necessary to learn the Google Sheets FILTER function before following the G-Query tutorial below. Understanding the FILTER function could certainly help you grasp the differences.

The initial step is to have sample data. Simply enter the data below into a new Google Sheets file in the range A1:F16 and name the tab “Master,” or use the provided button to copy the sample sheet.

Sample Sheet

Query Function as an Alternative to Filter Function - Example Dataset

You should name this range (not required if you use my sample sheet as I have already named it) to simplify the formula. Naming ranges is the most effective way to improve the readability of a formula.

To achieve this, navigate to the Data menu and select Named Ranges. Assign the range name “sourcemaster” as shown below.

Add Named Ranges for Query Function

Note: You can now utilize LET to name ranges directly within the formula itself.

Filter Text Field

You can observe various age groups listed under the “age_group” field. I am utilizing the QUERY function to filter the “age_group” field for the group “11-17” in a new sheet in the same Google Sheets file/Workbook.

Formula:

=QUERY(sourcemaster, "select A, B, C, D, E, F where B='11-17' ", 1)

In the above query formula, “sourcemaster” is the named range you just created in the “Master” sheet, and A, B, C, D, E, and F are the columns that you want to extract.

The QUERY function syntax is as follows:

QUERY(data, query, [headers])

As mentioned earlier, you can use the QUERY function as an alternative to the FILTER function in Google Sheets. Here is the equivalent FILTER formula:

=FILTER(sourcemaster, CHOOSECOLS(sourcemaster, 2)="11-17")

If you analyze the outputs, you can see that the QUERY retains the header, whereas the FILTER doesn’t.

If you wish to learn more about the FILTER function, refer to our tutorial on filtering data into separate sheets in Google Sheets.

Filter Date Field

In the previous Google Sheets QUERY formula, we applied a text field as the criterion, namely “11-17”. When utilizing a date as a criterion in QUERY, follow the formula below.

=QUERY(sourcemaster, "select A, C where F = date '1999-09-20'", 1)

I employed the QUERY function to filter the “dob” (date of birth) field in the data on the sheet “Master”. The resulting output will include only the names and genders.

Here is the alternative FILTER function:

=FILTER(CHOOSECOLS(sourcemaster, {1, 3}), CHOOSECOLS(sourcemaster, 6)=DATE(1999, 9, 20))

Refer to the following two tutorials to gain a comprehensive understanding of correctly using the date as a criterion in the QUERY function:

  1. Convert Date to String Using the Long-winded Approach in Google Sheets
  2. How to Use Date Criteria in Query Function in Google Sheets

Filter Numeric Field

If you wish to use a number as a criterion in QUERY, follow the example below.

Using the same sample data as above, let’s apply the function to filter the data based on the “age” field.

=QUERY(sourcemaster, "select A, B, C, D, E, F where E = 24", 1)

This formula filters the “age” field where the age of members is 24.

Here is the equivalent FILTER formula:

=FILTER(sourcemaster, CHOOSECOLS(sourcemaster, 5)=24)

Conclusion

In all the examples above, we specified the criterion within the formula. However, you can use a cell reference instead. To learn more about using cell references in Google Sheets QUERY, refer to: “How to Use Cell Reference in Google Sheets Query.”

I hope you have gained an understanding of how to use the QUERY function as an alternative to the FILTER function in Google Sheets.

The provided examples are straightforward, especially if you create the sample dataset first and apply the formulas on your own.

For a more in-depth understanding of this function, please refer to the tutorials below:

  1. How to Use Month Function in Google Sheets Query
  2. Filter by Month and Year in Query in Google Sheets
  3. How to Use the Datediff Function in Google Sheets Query
  4. How to Use LIKE String Operator in Google Sheets Query
  5. CONTAINS Substring Match in Google Sheets Query for Partial Match
  6. How to Use Not Equal to in Query in Google Sheets
  7. How to Use Arithmetic Operators in Query in Google Sheets
  8. How to Sum, Avg, Count, Max, and Min in Google Sheets Query
  9. How to Use Query with Importrange in Google Sheets
  10. STARTS WITH and NOT STARTS WITH Prefix Match in Google Sheets Query
  11. Ends With and Not Ends With Suffix Match in Query
  12. How to Use And, Or, and Not in Google Sheets Query
  13. Simple Comparison Operators in Google Sheets Query
  14. How to Use Multiple OR in Google Sheets Query
  15. How to Use DateTime in Query in Google Sheets
  16. Examples of the Use of Literals in Query in Google Sheets
  17. Multiple CONTAINS in WHERE Clause in Google Sheets Query
  18. How to Offset Match Using Query in Google Sheets
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

5 COMMENTS

  1. Awesome Prashant! Thanks a lot! A new issue arises from this, now that I’ve got the value I want when I try to run a script to move this value into another sheet, the formula got copied over instead of the value.

    Appreciate your advice Prashant.

  2. Hi Prashant, I have a different scenario having to use both Query and Filter. Kindly look at the attached link to my sheet.

    In the “Form Responses” sheet there will be multiple columns for Student Name, only one of these columns will have the name (selected based on their class).

    I created the “MD” sheet to pull out all the data but only need to show the name in 1 column.

    My challenge is: in the “MD” sheet, I use the Filter formula in Column A, I use Query for Column B to D. Whenever new data comes in, the Filter formula cell reference will increase by 1 row hence referred to the empty row and return empty cell in Col A. How can I fix this, please? Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here