ISBETWEEN in Google Sheets: Inclusive and Exclusive Range Checks
Use ISBETWEEN in Google Sheets with documented endpoint defaults, mixed inclusive bounds and nonoverlapping numeric bands, plus reproducible examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =ISBETWEEN(20,10,20) has expected result TRUE. ISBETWEEN checks whether a value lies between a lower and upper bound. Both endpoints are included by default.
Syntax and documented defaults
=ISBETWEEN(value_to_compare, lower_value, upper_value,
[lower_value_is_inclusive], [upper_value_is_inclusive])
| Argument | Meaning | Required or default |
|---|---|---|
value_to_compare |
Value being tested | Required |
lower_value |
Lower endpoint | Required |
upper_value |
Upper endpoint | Required |
lower_value_is_inclusive |
Include equality at the lower endpoint | Optional; TRUE |
upper_value_is_inclusive |
Include equality at the upper endpoint | Optional; TRUE |
Google documents these two independent TRUE defaults in its ISBETWEEN reference. Use explicit TRUE or FALSE flags when the boundary policy is important to the reader.
With three arguments, the intended numeric condition is lower ≤ value ≤ upper. With both flags FALSE, it is lower < value < upper. A four-argument call changes the lower flag while leaving the upper flag at its documented TRUE default.
Worked example: an acceptable temperature band
Enter this table in A1:B6:
| Row | A: Sample | B: Temperature |
|---|---|---|
| 1 | Sample | Temperature |
| 2 | A | 17 |
| 3 | B | 18 |
| 4 | C | 20 |
| 5 | D | 22 |
| 6 | E | 23 |
For this illustrative process, acceptable readings are 18 through 22 inclusive. In C1 enter In band; then fill this from C2 to C6:
=ISBETWEEN(B2,18,22)
Expected results: FALSE, TRUE, TRUE, TRUE, FALSE. The 18 and 22 readings pass because both endpoints are included. This is a synthetic numerical example, not a safety or health specification.
To label the result, use:
=IF(ISBETWEEN(B2,18,22,TRUE,TRUE),"In band","Review")
The explicit flags preserve the same expected results while making the policy visible in the formula.
Check each endpoint deliberately
For the numbers 10 and 20 as bounds:
| Formula | Expected result |
|---|---|
=ISBETWEEN(10,10,20) |
TRUE |
=ISBETWEEN(20,10,20) |
TRUE |
=ISBETWEEN(10,10,20,FALSE) |
FALSE |
=ISBETWEEN(20,10,20,FALSE) |
TRUE |
=ISBETWEEN(20,10,20,TRUE,FALSE) |
FALSE |
=ISBETWEEN(15,10,20,FALSE,FALSE) |
TRUE |
The fourth example is especially useful: omitting the fifth argument does not copy the fourth argument’s FALSE value. The upper endpoint keeps its own TRUE default.
Advanced application: bands without double counting
Suppose two reporting bands are 10 up to but not including 20, then 20 up to but not including 30. Enter a test quantity in E2 and use:
=ISBETWEEN(E2,10,20,TRUE,FALSE)
=ISBETWEEN(E2,20,30,TRUE,FALSE)
For E2 equal to 10, expected results are TRUE, FALSE. For 20, they are FALSE, TRUE. For 30, they are FALSE, FALSE. The shared boundary 20 belongs to only the second band.
If both bands included both endpoints, 20 would pass both tests. Half-open bands prevent that overlap. Decide separately whether the last band should include its final endpoint; changing only the final upper flag to TRUE would include 30 there.
For adjustable limits, place the lower bound in G1 and upper bound in H1, then fill =ISBETWEEN(B2,$G$1,$H$1,TRUE,TRUE) down the measurement rows. Absolute references keep the same bounds for every sample.
Errors, limits and troubleshooting
Verify that the lower value really is the intended lower bound and that all three values use the same units. The reviewed help does not specify a complete swapped-bound or nonnumeric-input error matrix, so do not depend on undocumented automatic reordering.
If a value visually matches an endpoint but fails the test, inspect its stored precision. Formatting may hide a small difference. If rounding is part of the process rule, apply it deliberately and document the precision rather than adjusting the bounds until the result changes.
The individual page does not establish every blank-cell, text, Boolean coercion or array-expansion behavior. Native error tokens for those cases were not verified. These examples are numeric scalar checks, with Boolean inclusion flags.
Availability and verification
ISBETWEEN is documented for Google’s rolling Sheets service. Original introduction date, first version and last functioning numbered release were not established. Its named-function syntax and endpoint defaults should not be assumed in another spreadsheet application.
NOT_TESTED_IN_GOOGLE_SHEETS. Google’s individual ISBETWEEN page was inspected on October 17, 2026. Every boundary result was independently checked against the documented inequalities; no native Sheets execution or screenshot is claimed.