ISBETWEEN
TheISBETWEEN
formula is an operator that checks if a given value is between two other values. It returns a boolean value of TRUE if the value to compare is between the lower and upper values (inclusive or exclusive based on the optional parameters). Otherwise, it returns FALSE. This formula is commonly used in conditional formatting rules or data validation rules.
 How to use
ISBETWEEN
formula?  Examples of using
ISBETWEEN
formula ISBETWEEN
formula not working? Similar formulas to
ISBETWEEN
Usage
Use the ISBETWEEN
formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
 value_to_compare (required):
The value that will be compared to the range of values.  lower_value (required):
The lower bound of the range of values to compare against.  upper_value (required):
The upper bound of the range of values to compare against.  lower_value_is_inclusive (optional):
An optional boolean value that determines whether the lower bound is inclusive or exclusive. Default is TRUE (inclusive).  upper_value_is_inclusive (optional):
An optional boolean value that determines whether the upper bound is inclusive or exclusive. Default is TRUE (inclusive).
Examples
Here are a few example use cases that explain how to use theISBETWEEN
formula in Google Sheets.
Highlighting values within a range
You can use ISBETWEEN
to highlight values in a range that fall within a specific range of values. For example, you can use this formula to highlight all values in a column that are between 5 and 10.
Data validation
You can use ISBETWEEN
as part of a data validation rule to ensure that a cell's value falls within a specific range of values. For example, you can use this formula to validate that a user's input falls within a specific range.
Counting values within a range
You can use ISBETWEEN
as part of a COUNTIF formula to count the number of values in a range that fall within a specific range of values. For example, you can use this formula to count the number of values in a column that are between 5 and 10.
Common Mistakes
ISBETWEEN
not working? Here are some common mistakes people make when using the ISBETWEEN
Google Sheets Formula:
Forgetting to include the value to compare
One of the most common mistakes when using ISBETWEEN
formula is to forget to include the value to compare.
Incorrectly setting the bounds as inclusive or exclusive
It's important to set the lower_value_is_inclusive
and upper_value_is_inclusive
parameters correctly, or the formula may return an incorrect result.
Using nonnumeric values for comparison
The ISBETWEEN
formula only works with numeric values, so if you attempt to use it with nonnumeric values, it will return an error.
Reversing the order of the lower and upper bounds
It's important to specify the lower bound before the upper bound, or the formula may return an incorrect result.
Not accounting for rounding errors
When using the ISBETWEEN
formula with floating point numbers, it's important to account for rounding errors in the comparison.
Related Formulas
The following functions are similar to ISBETWEEN
or are often used with it in a formula:

IF
The
IF
formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions. 
AND
The
AND
function in Google Sheets is a logical function that returns TRUE if all of the logical expressions in the argument are TRUE, and FALSE if any of the logical expressions are FALSE. It can be used to test multiple conditions and evaluate whether they are all true or not. 
OR
The
OR
formula is a logical formula that returns TRUE if at least one of the provided logical expressions is TRUE. It returns FALSE if all logical expressions are FALSE. This formula is commonly used in conjunction with other logical formulas, such asAND
, to build more complex logical statements. 
COUNTIF
The
COUNTIF
formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria. 
AVERAGEIF
The
AVERAGEIF
function calculates the average of a range of cells that meet a specified criteria. It is commonly used when working with large datasets to quickly calculate the average of a subset of data. The function takes a range of cells to evaluate (criteria_range), a string or value to compare against (criterion), and an optional range of cells to average (average_range). If the average_range is not specified, the function will use the same range as the criteria_range.
Learn More
You can learn more about the ISBETWEEN
Google Sheets function on Google Support.