Simple Comparison Operators in Google Sheets Query

Published on

This post contains a few formula examples that explain the use of the simple comparison operators in Google Sheets Query.

They are seven in number: <, <=, >, >=, =, !=, and <>.

In this tutorial, you may find basic examples of using simple comparison operators in the QUERY function.

It’s because if you use comparison operators, simple or complex, there may come the use of literals, i.e., the values used for comparison.

Their usage is different for numbers, strings, and dates.

For example, a string literal should be enclosed in single or double quotes (I usually use single quotes). Such details you can find in one of my other tutorials and here it is – Examples of the Use of Literals in Query in Google Sheets.

In the following examples, I am not digging deep into the details of using literals in Google Sheets Query.

In addition to the above, there are a few (string) comparison operators. We will discuss that in the last part of this tutorial under the subtitle “Complex Comparison Operators.”

All (Seven) Simple Comparison Operators in Google Sheets Query

Sample Data: Marks of students in three subjects in different (first, second, and third) terms.

Simple Comparison Optrs. in Query - Sample

I’ve included seven Query formulas below. Let’s go to them one by one.

1. Less Than (<) in Sheets Query.

=query(A1:E,"Select * where C < 95",1)

2. Greater Than (>) in Sheets Query.

=query(A1:E,"Select * where D > 80",1)
Comparison Operators in Google Sheets Query

3. Less Than or Equal to (<=) in Sheets Query.

=query(A1:E,"Select * where D <= 80",1)

4. Greater Than or Equal to (>=) in Sheets Query.

=query(A1:E,"Select * where E >= 80",1)

5. Equal to (=) in Sheets Query.

=query(A1:E,"Select * where B = 'First'",1)

6. Not Equal (<>) to in Sheets Query.

=query(A1:E,"Select * where B <> 'First'",1)

7. Not Equal to (<>) Equivalent.

=query(A1:E,"Select * where B != 'First'",1)

Please note that comparing to null in a column is done using is null or is not null. Here is an example.

=query(A1:E,"Select * where B is not null",1)

I hope the above examples help you learn the use of simple comparison operators in Google Sheets Query.

Please don’t forget to learn the literals usage as mentioned at the beginning of this tutorial.

To use multiple comparison operators in Google Sheets Query, in the same column or different columns, you must know How to Use And, Or, and Not in Google Sheets Query.

Complex Comparison Operators

The below five operators are for string comparison. But you can compare numbers also to some extent.

  1. Contains.
  2. Matches.
  3. Starts with.
  4. Ends with.
  5. Like.

I have included all the required tips in this tutorial to help you learn simple and complex comparison operators in Google Sheets Query.

That’s all for now! See you back again with another Google Sheets tutorial.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. With years of experience working with Google Sheets and Excel for multinational firms in Mumbai and Dubai, he has been blogging since 2012, offering practical, real-world spreadsheet solutions that professionals rely on. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.