This post explains how to use the ISBETWEEN function in Google Sheets.

If this function is not yet available in your Sheets, please wait for the update to be completely rolled out as it’s new.

ISBETWEEN is categorized under “Operator” in Google Sheets.

Purpose: We can use this function to compare a value between two other values and return TRUE or FALSE based on the test.

Before, we depend on the comparison operators >= and <= or the corresponding operator type functions GTE and LTE for the same purpose. Further, we may require the AND logical operator.

It’s somewhat complex to write an array formula (a single formula for multiple rows) using the above operators.

The ISBETWEEN function in Google Sheets is going to simplify things for you.

I’ll explain all the above using examples below. Before that, it is time to go to the syntax of the function.

ISBETWEEN Function in Google Sheets – Syntax and Arguments

Syntax:

ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])

Arguments:

There are five arguments in ISBETWEEN, out of which the last two are optional.

I think all the arguments are self-explanatory.

Regarding the two optional arguments, we can use TRUE or FALSE. TRUE means the lower/upper value is inclusive and FALSE means exclusive.

By default, the optional arguments are set to TRUE. So, use it only if you want to exclude all or either of the lower/upper values in ISBETWEEN.

Examples to the Use of ISBETWEEN Function in Google Sheets

In non-array form, we can use a number, date, or a letter in the value_to_compare part of the formula.

Let’s start with a number.

Numeric Value in Value_to_Compare

Assume, cell B2 contains the value 50. I want to test whether this value is between 25 and 75, both of the values inclusive.

For that, I can use the below ISBETWEEN formula in Google Sheets.

=ISBETWEEN(B2,25,75)

I haven’t used the optional arguments because the lower value 25 and the upper value 75 are inclusive.

Earlier Workaround Solutions:

Before the introduction of the ISBETWEEN function in Google Sheets, we were using the below workaround formulas.

=and(B2>=25,B2<=75)

Or;

=if((B2>=25)*(B2<=75)=1,TRUE,FALSE)

In the above two alternatives to the ISBETWEEN function, the latter only supports array formula use.

Let’s come back to our examples.

In cell B3, I have put 25. The formula =ISBETWEEN(B3,25,75) will return TRUE whereas the formula =ISBETWEEN(B3,25,75,FALSE,FALSE) will return FALSE.

The latter formula returns the Boolean value FALSE because the fourth and fifth parameters, i.e., lower_value_is_inclusive and upper_value_is_inclusive, are set to FALSE. That means 25 and 75 are exclusive.

A Few More Examples:

Date in Value_to_Compare

Assume cell A2 contains the date 16-Mar-2021.

I want to test whether this date is between 1-Jan-2021 and 31-Mar-2021, both the dates inclusive.

Let’s see how to use the ISBETWEEN function in Google Sheets here.

=ISBETWEEN(A2,date(2021,1,1),date(2021,3,31))

Please note that the second and third arguments, i.e., the lower_value and upper_value, must be specified using the DATE function in the format date(year, month, day) to avoid date format error.

A Few More Examples:

Letter (Alphabets) in Value_to_Compare

I don’t know how many of you will use or will find this feature useful.

Let’s check whether the alphabets in cell range B1:B26 are between the letters “F” and “L”.

In cell B1, enter the following non-array formula and copy-down.

=ISBETWEEN(B1,"F","L")

The formulas in cell range C6:C12 return TRUE because the letters in corresponding rows in column B are between F and L, both inclusive.

IF with ISBETWEEN Function in Google Sheets

In most cases, you will find the combination of IF logical with the ISBETWEEN function more practical. Here is one example.

Assume, members of an organization have to submit a form on or before 10-Mar-2021.

I want to test the form submission date and return the string “Reject” if the form is submitted after the specified date.

Here is an example of the IF and ISBETWEEN combined use.

The cell in question is cell A2.

The formula =ISBETWEEN(A2,0,date(2021,3,10)) will return TRUE for any entry submitted before 11-Mar-2021.

To return “Reject”, put the string in the value if_false_part of the IF function [Syntax: IF(logical_expression, value_if_true, value_if_false)]. You can leave the value if_true_part blank.

=if(ISBETWEEN(A2,0,date(2021,3,10))=TRUE,,"Reject")

ISBETWEEN Array Formula Use

The advantage of the ISBETWEEN function in Google Sheets over the comparison operators is its easiness in Array Formula use.

Please see image # 3 above.

There, in the cell range C1:C26, I have used several ISBETWEEN formulas.

We can use the below Array Formula in cell C1 to replace all the formulas in C1:C26.

Steps:-

First, please make C1:C26 empty.

The earlier formula in C1 was =ISBETWEEN(B1,"F","L").

Replace B1 with B1:B26 and wrap the formula with ArrayFormula.

So the formula will be;

=ArrayFormula(ISBETWEEN(B1:B26,"F","L"))

In open ranges like B1:B, you should use a formula that limits the formula output only in the non-blank rows.

This will help.

=ArrayFormula(if(len(B1:B),ISBETWEEN(B1:B,"F","L"),))

Alternative:

=ArrayFormula(if(len(B1:B),if((B1:B>="F")*(B1:B<="L")=1,TRUE,FALSE),))

Related: Find Whether Test Results Fall within Their Limit in Google Sheets.

Conclusion

In case the formula returns #NUM!, please see the lower_value, and upper_value values are of the same value type.

For example, if the lower value is one and the upper value is a hundred, input them as 1, 100, not “1”, 100.

In addition to the above examples, we can use the ISBETWEEN function in Google Sheets to write custom formula rules in conditional formatting.

For example, to highlight the entire rows if the numbers in A2:A are between 1 and 100, we can use the below rule.

=isbetween($A2,1,100)

That’s all. Thanks for the stay. Enjoy!