Google Sheets intermediate query select filter where

How to Use QUERY to Select and Filter Data in Google Sheets?

Query Select with Where is a Google Sheets function that selects columns a and b from the range a1:c10 where the values in column c are greater than 100.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

The QUERY function allows you to perform SQL-like operations in Google Sheets, making data manipulation easier and more intuitive.

Generate This Formula Browse Library

The Formula

Prompt

"Use the QUERY function to SELECT specific columns and filter rows with a WHERE clause, like a SQL query inside Google Sheets"

Google Sheets 
=QUERY(A1:C10, "SELECT A, B WHERE C > 100", 1)

This formula selects columns A and B from the range A1:C10 where the values in column C are greater than 100.

Step-by-Step Breakdown

  1. Define the data range to query (A1:C10).
  2. Use the SELECT clause to specify which columns to return (A, B).
  3. Apply the WHERE clause to filter rows based on a condition (C > 100).
  4. The last argument (1) indicates that the first row contains headers.

Edge Cases & Warnings

  • If the data range contains no rows meeting the condition, the output will be empty.
  • Using a non-existent column in the SELECT clause will result in an error.
  • If the range is incorrectly specified, the formula will return an error.
  • Filtering with text conditions may lead to unexpected results if case sensitivity is not considered.

Examples

Prompt

"Range A1:C10 with values: A1=Name, B1=Age, C1=Score; A2=John, B2=25, C2=150; A3=Jane, B3=30, C3=90"

Google Sheets 
Returns John, 25
Prompt

"Range A1:C10 with values: A1=Product, B1=Price, C1=Stock; A2=Item1, B2=20, C2=120; A3=Item2, B3=30, C3=80"

Google Sheets 
Returns Item1, 20

Frequently Asked Questions

Can I use multiple conditions in the WHERE clause?

Yes, you can combine conditions using AND or OR.

What happens if the data range is empty?

The formula will return an empty result.

Is the QUERY function case-sensitive?

Yes, comparisons in the WHERE clause are case-sensitive.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples