Google Sheets ISBETWEEN Function: Syntax, Examples & Tips

What Is ISBETWEEN?

ISBETWEEN tests whether a value sits inside a range defined by a lower and an upper bound. It returns TRUE if it does and FALSE if it doesn't. It saves you from writing two comparisons joined with AND, and its optional arguments let you decide precisely whether the endpoints themselves belong to the range.

How ISBETWEEN Relates to Comparison Operators

There is no single operator for "between", so ISBETWEEN replaces a pair of comparisons. With the defaults (both bounds inclusive):

=ISBETWEEN(A2, 10, 20)

is equivalent to

=AND(A2>=10, A2<=20)

Setting the inclusive flags to FALSE switches >= to > and <= to <:

Basic Examples

Inclusive range

=ISBETWEEN(B2, 1, 100)

Returns TRUE for any value from 1 to 100, including 1 and 100.

Half-open range for bucketing

=ISBETWEEN(B2, 50, 100, TRUE, FALSE)

Returns TRUE for values from 50 up to, but not including, 100. Half-open ranges are ideal for buckets because a value on a boundary (exactly 100) lands in only one bucket.

Date window

=ISBETWEEN(A2, DATE(2026,7,1), DATE(2026,9,30))

Returns TRUE for dates in the third quarter of 2026. Because dates are serial numbers, ISBETWEEN works on them directly.

Bounds from cells

=ISBETWEEN(C2, $F$1, $G$1)

Lets you change the range without editing the formula.

Using ISBETWEEN with Ranges

ISBETWEEN evaluates a single value, but you can apply it to a whole column with ARRAYFORMULA, or use it directly inside FILTER:

=FILTER(A2:D500, ISBETWEEN(C2:C500, 1000, 5000))

This returns every row whose value in column C is between 1,000 and 5,000.

To count matches, combine it with SUMPRODUCT or COUNTIF-style logic:

=SUMPRODUCT(--ISBETWEEN(C2:C500, 1000, 5000))

Common Use Cases

• Date filtering: Keep transactions inside a reporting period.
• Score bands: Assign grades or tiers based on numeric ranges.
• Tolerance checks: Verify a measurement is within specification limits.
• Age brackets: Segment customers into age groups.
• Conditional formatting: Highlight values inside a target band with a custom formula such as =ISBETWEEN($B2, 90, 110).

Common Errors

• Bounds in the wrong order: If lower_value is greater than upper_value, nothing can satisfy both conditions and the result is always FALSE. Make sure the smaller value comes first.
• Text instead of numbers: ISBETWEEN is meant for numeric comparison, so a value Sheets did not recognise as a number or date, such as an unparsed date string, will not compare as expected. Convert with VALUE or DATEVALUE.
• Timestamps at the upper bound: A date-time later on the end date is greater than the date itself. Use the next day with upper_value_is_inclusive set to FALSE, or compare INT(A2).
• Blank cells: An empty cell is treated as 0, so it will return TRUE if 0 lies inside your range.

Tips and Best Practices

• Leave the inclusive flags out unless you need them — the default (both inclusive) matches how most people say "between".
• For adjoining buckets, make each one inclusive on the bottom and exclusive on the top so no value is counted twice.
• Keep bounds in labelled cells for easier maintenance.
• ISBETWEEN is often clearer than nested AND(GTE(...), LTE(...)) for reviewers.

Related Functions

GTE and LTE for single-sided inclusive checks, GT and LT for strict ones, AND for combining conditions, and COUNTIFS for counting values between two bounds across a range (=COUNTIFS(C:C, ">=1000", C:C, "<=5000")).