Are you tired of using complex array formulas to compare values between two other values in Google Sheets? Well, worry no more! The ISBETWEEN function is here to simplify things for you. In this article, we’ll explore how to use the ISBETWEEN function in Google Sheets and provide some handy examples along the way.
ISBETWEEN Function in Google Sheets – Syntax and Arguments
Before we dive into the examples, let’s familiarize ourselves with the syntax and arguments of the ISBETWEEN function.
Syntax:
ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])
Arguments:
There are five arguments in the ISBETWEEN function, with the last two being optional. The arguments are pretty self-explanatory, but let’s quickly go through them:
value_to_compare: The value you want to compare.lower_value: The lower value of the range you want to compare.upper_value: The upper value of the range you want to compare.lower_value_is_inclusive(optional): Set to TRUE if the lower value is inclusive, or FALSE if it’s exclusive. Defaults to TRUE.upper_value_is_inclusive(optional): Set to TRUE if the upper value is inclusive, or FALSE if it’s exclusive. Defaults to TRUE.
Now that we have the basics down, let’s jump into some examples!
Examples of Using the ISBETWEEN Function in Google Sheets
Numeric Value in value_to_compare
Let’s start with a simple example. Assume that cell B2 contains the value 50, and we want to test whether this value is between 25 and 75 (inclusive). Here’s how you can use the ISBETWEEN function for this:
=ISBETWEEN(B2,25,75)
No need to use any optional arguments in this case because both the lower and upper values are inclusive. In the past, we had to rely on comparison operators like >= and <=, or their corresponding functions GTE and LTE, to achieve the same result. But with the ISBETWEEN function, it becomes much simpler.
Date in value_to_compare
Now, let’s move on to using dates in the value_to_compare part of the formula. Assume that cell A2 contains the date 16-Mar-2021, and we want to test whether this date falls between 1-Jan-2021 and 31-Mar-2021 (both inclusive). Here’s how to do it:
=ISBETWEEN(A2,date(2021,1,1),date(2021,3,31))
Remember to use the DATE function to specify the lower and upper values in the format date(year, month, day).
Letter (Alphabets) in value_to_compare
Lastly, let’s explore something a bit different. Imagine you want to 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 it down:
=ISBETWEEN(B1,"F","L")
The formulas in the corresponding rows in column C will return TRUE if the letters in column B are between F and L (both inclusive).
IF with ISBETWEEN Function in Google Sheets
In most cases, you’ll find combining the IF logical function with the ISBETWEEN function more practical. Here’s an example to illustrate how you can use them together.
Assume that members of an organization have to submit a form on or before 10-Mar-2021. You want to test the form submission date and return the string “Reject” if the form is submitted after the specified date. Here’s how you can achieve this:
In cell A2, use the formula =ISBETWEEN(A2,0,date(2021,3,10)). This formula 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. The complete formula will look like this:
=IF(ISBETWEEN(A2,0,date(2021,3,10))=TRUE,,"Reject")
Conclusion
The ISBETWEEN function in Google Sheets is a powerful tool that simplifies comparing values between two other values. Whether you’re dealing with numbers, dates, or even letters, this function has got you covered. Remember to use the optional arguments if you want to exclude certain values from the comparison.
To learn more about Google Sheets and other useful tips and tricks, visit Crawlan.com. Happy sheeting!
