How to Use the ISBETWEEN Function in Google Sheets to Validate Numerical and Date Ranges

When auditing financial ledgers, evaluating student grades, or filtering inventory levels in Google Sheets, spreadsheet creators frequently need to check whether a specific value falls between two defined boundaries. Historically, testing boundary conditions required combining multiple logical tests using compound formulas such as AND(A2 >= 10, A2 <= 50). Google Sheets provides a streamlined native function specifically designed for this purpose: ISBETWEEN. This logical function simplifies complex formulas, improves readability, and integrates cleanly with conditional formatting and array operations.

Syntax and Parameter Overview of ISBETWEEN

The ISBETWEEN function tests whether a given value lies between a lower and an upper bound. The formal syntax is structured as follows:

=ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_is_inclusive], [upper_is_inclusive])

Parameter Definitions

• value_to_compare (Required): The number, date, or cell reference you wish to evaluate.
• lower_value (Required): The lower boundary of the evaluation interval.
• upper_value (Required): The upper boundary of the evaluation interval.
• lower_is_inclusive (Optional, Default: TRUE): A boolean flag determining whether the lower boundary itself is considered inside the interval (>= vs >).
• upper_is_inclusive (Optional, Default: TRUE): A boolean flag determining whether the upper boundary itself is considered inside the interval (<= vs <).

Basic Usage: Testing Numerical Values

In standard data validation tasks, you frequently want to confirm that a metric sits within acceptable quality tolerance limits:

Inclusive Evaluation (Default Behaviour)

To check if a score in cell B2 falls between 50 and 75, including both 50 and 75:

=ISBETWEEN(B2, 50, 75)

If B2 contains 50, 62.5, or 75, the formula returns TRUE. If B2 is 49.9 or 75.1, it returns FALSE.

Exclusive Evaluation (Strict Inequality)

If your business logic requires values to be strictly greater than 50 and strictly less than 75, pass FALSE to both optional parameters:

=ISBETWEEN(B2, 50, 75, FALSE, FALSE)

Under this configuration, an exact value of 50 or 75 returns FALSE, while 50.01 returns TRUE.

Evaluating Date and Timestamp Ranges

The ISBETWEEN function seamlessly handles date objects, allowing you to audit project deadlines or sales quarters without complex date parsing:

=ISBETWEEN(A2, DATE(2026, 1, 1), DATE(2026, 3, 31))

This formula checks whether the date stored in cell A2 falls within the first calendar quarter of 2026. Because date comparisons rely on underlying serial integers, the function handles timezones and chronological sequencing reliably.

Combining ISBETWEEN with IF for Business Logic

While ISBETWEEN outputs a boolean TRUE or FALSE on its own, nesting it inside an IF statement lets you generate informative textual labels or automated workflow triggers:

=IF(ISBETWEEN(C2, 90, 100), "Distinction", IF(ISBETWEEN(C2, 60, 89.9), "Pass", "Resit Required"))

This approach keeps nested logic concise and legible compared to traditional chained AND() expressions.

Using ISBETWEEN in Conditional Formatting Rules

Highlighting cells that fall within an expected range is a common visual dashboard requirement. To apply conditional formatting using ISBETWEEN:

1. Select your target data range (for example, B2:B50).
2. Click Format in the top menu and select Conditional formatting.
3. In the formatting sidebar, under Format rules, open the drop-down menu and choose Custom formula is.
4. Enter the relative formula:

   =ISBETWEEN(B2, 1000, 5000)

5. Choose your preferred formatting style (such as a subtle green fill and dark text) and click Done.

All values within the specified numeric range immediately update with the selected styling.

Array Processing with BYROW and MAP

To evaluate an entire column of numbers without dragging the formula down manually, pair ISBETWEEN with MAP or BYROW:

=MAP(B2:B50, LAMBDA(val, IF(ISBLANK(val), "", ISBETWEEN(val, 20, 80))))

This formula spills boolean validation results dynamically down column C while automatically skipping empty cells, preventing unnecessary clutter in blank template rows.

Key Operational Rules and Common Pitfalls

To avoid unexpected calculation errors, keep these operational rules in mind:

• Order of Bounds: The second argument (lower_value) must be mathematically smaller than or equal to the third argument (upper_value). If you enter bounds in reverse order (e.g. ISBETWEEN(A2, 100, 50)), the function will always return FALSE.
• Data Type Consistency: Ensure that the evaluated cell and both boundary arguments share the same data type. Comparing a numeric string (such as text formatted "50") against numeric boundaries can produce misleading comparison outcomes.
• Handling Blank Cells: An empty cell is interpreted by Google Sheets as numerical zero (0). If your interval includes zero, a blank cell evaluates to TRUE. Wrap your expression with IF(ISBLANK(A2), "", ISBETWEEN(...)) to prevent unintended matches.