Comparison Operators in Google Sheets (A Complete Guide)

Sumit

Last updated May 2, 2023

Google Sheets lets you use six comparison operators to make comparisons between values and return a TRUE or FALSE value.

These operators can be easily incorporated into IF statements or used as they are.

They are also used in filters, conditional formatting operations, comparing columns, and datasets, and much more.

In this article, we will introduce you to 6 types of comparison operators, what they do, how they are used, and also how they can be combined to create more complex conditions.

This Article Covers:

What are Comparison Operators?

Comparison operators work on a pair of values and compare them to tell us whether the values are equal, not equal, or if one is more than and/or equal to the other.

The values that the comparison operators work on can be numeric values, date and time values, and in some cases, text values too.

The comparison operator simply evaluates the value to its left-hand side in comparison to the value on its right.

<left side> (comparison operator) <right side> = TRUE / FALSE

They return a TRUE or FALSE Boolean value depending on the outcome of the comparison and this result can then be used to determine if a transformation needs to be executed on a row/column/cell or not.

Types of Comparison Operators

There are 6 comparison operators that can be used in Google Sheets. Following are the names of the operators, along with the symbol used to represent the operation:

Equal to (=)
Not Equal to (<>)
Less than(<)
Greater than(>)
Less than or Equal to (<=)
Greater than or Equal to (>=)

Here’s what each of these comparison operators does:

Equal to (=):

This operator compares and checks if the left side is equal to the right side value.

The syntax for the ‘Equal to’ operation is:

=(value1 = value2)

Where value1 and value2 are the two values being compared and can be numbers, text, strings, date/time values, Boolean values or references to cells containing any of these values.

Let us take an example to see how the ‘Equal to’ operator works:

In the above image, it can be observed that:

the operation =(A2=B2) will return the value TRUE since A2 has the same value as B2.
the operation =(A3=B3) will return the value FALSE since the value in A3 is not the same as the value in B3.
Similarly, the operation =(A4=B4) will return the value FALSE for the same reason.

Not Equal to (=):

This operator compares and checks if the left side is NOT equal to the right side value.

The syntax for the ‘Not Equal to’ operation is:

=(value1 <> value2)

Where value1 and value2 are the two values being compared and can be numbers, text, strings, date/time values, Boolean values, or references to cells containing any of these values.

Let us take an example to see how the ‘Not Equal to’ operator works:

In the above image, it can be observed that:

the operation =(A2<>B2) will return the value FALSE, since A2 has the same value as B2.
the operation =(A3<>B3) will return the value TRUE, since the value in A3 is not the same as the value in B3.
Similarly, the operation =(A4<>B4) will return the value TRUE for the same reason.

Notice that the ‘=’ and ‘<>’ operators are exactly the opposite of each other.

Less than (<):

This operator compares and checks if the left-side value is less than the right side value.

The syntax for the ‘Less than’ operation is:

=(value1 < value2)

Where value1 and value2 are the two values being compared.

Let us take an example to see how the ‘Less than’ operator works:

In the above image, it can be observed that:

the operation =(A2<B2) will return the value FALSE, since A2 has the same value as B2.
the operation =(A3<B3) will return the value FALSE, since the value in A3 is not less than the value in B3.
Similarly, the operation =(A4<B4) will return the value TRUE, since the value in A4 is less than the value in B4.

Greater than (>):

This operator compares and checks if the left-side value is greater than the right side value.

The syntax for the ‘Greater than’ operation is:

=(value1 > value2)

Where value1 and value2 are the two values being compared.

Let us take an example to see how the ‘Greater than’ operator works:

In the above image, it can be observed that:

the operation =(A2>B2) will return the value FALSE, since A2 has the same value as B2.
the operation =(A3>B3) will return the value TRUE, since the value in A3 is greater than the value in B3.
Similarly, the operation =(A4>B4) will return the value FALSE, since the value in A3 is not greater than the value in B3.

Less than or Equal to (<=):

This operator compares and checks if the left-side value is less than or equal to the right side value.

The syntax for the ‘Less than or Equal to’ operation is:

=(value1 <= value2)

Where value1 and value2 are the two values being compared.

Let us take an example to see how the ‘Less than or Equal to’ operator works:

Note: We put both ‘less than’ and ‘less than or equal to’ comparisons side by side so that you can clearly see the difference between the two operators.

In the above image, it can be observed that:

the operation =(A2<=B2) will return the value TRUE. As you can see, the value in A2 is not less than B2. However, the fact that A2 is equal to B2 makes the statement TRUE.
the operation =(A3<=B3) will return the value FALSE, since the value in A3 is not less than or equal to the value in B3.
Similarly, the operation =(A4<=B4) will return the value TRUE, since the value in A4 is less than the value in B4.

Greater than or Equal to (>=):

This operator compares and checks if the left-side value is greater than or equal to the right side value.

The syntax for the ‘Greater than or Equal to’ operation is:

=(value1 >= value2)

Where value1 and value2 are the two values being compared.

Let us take an example to see how the ‘Greater than or Equal to’ operator works:

Note: We put both ‘greater than’ and greater than or equal to’ comparisons side by side so that you can clearly see the difference between the two operators.

In the above image, it can be observed that:

the operation =(A2>=B2) will return the value TRUE. As you can see, the value in A2 is not greater than B2. However, the fact that A2 is equal to B2 makes the statement TRUE.
the operation =(A3>=B3) will return the value TRUE, since the value in A3 is greater than the value in B3.
Similarly, the operation =(A4>=B4) will return the value FALSE, since the value in A4 is not greater than or equal to the value in B4.

Alternative Functions that Work like Comparison Operators

Besides the 6 comparison operators listed above, Google Sheets also lets you use 6 comparison functions as alternatives. These work exactly the same way as comparison operators and only differ in their syntaxes.

We have listed below each of the comparison function alternatives along with their syntaxes. In each syntax, value1 and value2 are the values to be compared:

Comparison Operator	Comparison Function	Syntax
=	EQ	EQ(value1,value2)
<>	NE	NE(value1,value2)
<	LT	LT(value1,value2)
>	GT	GT(value1,value2)
<=	LTE	LTE(value1,value2)
>=	GTE	GTE(value1,value2)

Applications of Comparison Operators

Comparison operators can be applied to more or less any situation that requires a particular condition to be satisfied for something to happen. Here are some common areas where comparison operators can be applied:

Comparison operators are always used in conditional functions like IF, SUMIF, etc. For example, if you want to display the text “Yes” whenever the value in column B is “Sales”, then you can use comparison operators with an IF statement as follows:
Similarly, you can use comparison operators in other functions that require specifying a condition, like the SUMIF function, COUNTIF function, etc. For example, you can use comparison operators to find the number of employees from the Sales department as follows:
Finally, you can combine comparison operators with logical operators like OR, AND or NOT to create more complex conditions, as shown in the image below.

Related: Google Sheets COUNTIF

Combining Comparison Operators with Logical Operators (OR, AND, NOT)

Comparison operators can be combined together using logical operators, OR, AND or NOT to create more complex conditions. For example, consider the dataset below:

Logical operators like AND and OR work with two or more conditions. The AND operator returns TRUE if all conditions are met.

For example, the following condition returns a TRUE value if both conditions (B2=”Sales”) and (C2>=30000) are true:

=AND(B2 ="Sales",C2>=30000)

It is then wrapped in the IF formula to return Qualified if the AND formula result is TRUE and Not Qualified if the AND formula result is FALSE.

=IF(AND(B2 ="Sales",C2>=30000),"Qualified","Not Qualified")

If any one of these two conditions is false, then the value returned by the whole formula is also FALSE, which makes the IF formula return ‘Not Qualified’

On the other hand, The OR formula returns a TRUE if at least one of the conditions is met. For example, the following condition returns a TRUE value if either (B2=”Sales”) or (C2>=30000) is true.

=OR(B2="Sales",C2>=30000)

If any one of these two conditions is true, then the value returned by the whole formula is also TRUE.

Unlike the AND and OR operators, the NOT operator works with just one condition. All it does is negate the result of the condition. For example, if we type the following, it will return a FALSE value if the condition (B2=”Sales”) is TRUE.