# Google Sheets ISBETWEEN Function: Syntax, Examples & Tips

- URL: https://betteranalyst.com/google-sheets/isbetween-function
- Author: Better Analyst

Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation.

[Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst)

Checks whether a value falls between a lower and upper bound, with optional control over whether each bound is inclusive.

```
=ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])
```

Generate FormulaExplain Formula

One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.

Describe the ISBETWEEN you need in plain EnglishGenerate Formula

## Parameters

| Parameter | Required | Description |
| - | - | - |
| **value\_to\_compare** | Yes | The value to test. Can be a number, date, or cell reference. |
| **lower\_value** | Yes | The lower bound of the range. |
| **upper\_value** | Yes | The upper bound of the range. |
| **lower\_value\_is\_inclusive** | No | TRUE (default) if a value equal to **lower\_value** counts as inside the range; FALSE to exclude it. |
| **upper\_value\_is\_inclusive** | No | TRUE (default) if a value equal to **upper\_value** counts as inside the range; FALSE to exclude it. |

## 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 `<`:

| lower\_value\_is\_inclusive | upper\_value\_is\_inclusive | Equivalent test |
| - | - | - |
| TRUE (default) | TRUE (default) | `lower <= value <= upper` |
| FALSE | TRUE | `lower < value <= upper` |
| TRUE | FALSE | `lower <= value < upper` |
| FALSE | FALSE | `lower < value < upper` |

## 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](https://betteranalyst.com/google-sheets/gte-function) and [LTE](https://betteranalyst.com/google-sheets/lte-function) for single-sided inclusive checks, [GT](https://betteranalyst.com/google-sheets/gt-function) and [LT](https://betteranalyst.com/google-sheets/lt-function) for strict ones, [AND](https://betteranalyst.com/google-sheets/and-function) for combining conditions, and [COUNTIFS](https://betteranalyst.com/google-sheets/countifs-function) for counting values between two bounds across a range (`=COUNTIFS(C:C, ">=1000", C:C, "<=5000")`).

## Example use case

A marketing analyst wants only orders placed during a September promotion. With order dates in column A, =FILTER(A2:E1000, ISBETWEEN(A2:A1000, DATE(2026,9,1), DATE(2026,9,30))) returns every order from the campaign window, including the first and last days.

## Need more help with Google Sheets formulas?

Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis.

[Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai)

## Google Sheets how-to guides

- [How To Divide In Google Sheets](https://betteranalyst.com/google-sheets/how-to-divide-in-google-sheets)
- [How To Enable Macros In Google Sheets](https://betteranalyst.com/google-sheets/how-to-enable-macros-in-google-sheets)
- [How To Filter In Google Sheets](https://betteranalyst.com/google-sheets/how-to-filter-in-google-sheets)
- [How To Find Circular References In Google Sheets](https://betteranalyst.com/google-sheets/how-to-find-circular-references-in-google-sheets)
- [How To Find Duplicates In Google Sheets](https://betteranalyst.com/google-sheets/how-to-find-duplicates-in-google-sheets)
- [How To Freeze A Row In Google Sheets](https://betteranalyst.com/google-sheets/how-to-freeze-a-row-in-google-sheets)
- [How To Freeze Panes In Google Sheets](https://betteranalyst.com/google-sheets/how-to-freeze-panes-in-google-sheets)
- [How To Highlight Duplicates In Google Sheets](https://betteranalyst.com/google-sheets/how-to-highlight-duplicates-in-google-sheets)

## Explore all Google Sheets formulas

Browse our complete library of Google Sheets formula guides

Compare your options

## Decide with evidence, not a sales pitch.

Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.

1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)
2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)
3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)

AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.