# How to check if a number is between two values in Excel

Use AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range").

**Excel:** `=IF(AND(B2>=$F$1,B2<=$F$2),"In range","Out of range")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Batch | Temperature °C | Check |  | Low | 18 |
| T-1 | 19.5 |  |  | High | 24 |
| T-2 | 24.8 |  |  |  |  |
| T-3 | 21 |  |  |  |  |
| T-4 | 17.2 |  |  |  |  |
| T-5 | 24 |  |  |  |  |
| T-6 | 22.6 |  |  |  |  |

Formula in C2, filled to C7: `=IF(AND(B2>=$F$1,B2<=$F$2),"In range","Out of range")`

Result: In range; Out of range; In range; Out of range; In range; In range

## How it works

"Between" is two conditions: at least the low limit **and** at most the high limit. AND returns TRUE only when both hold. Using `>=` and `` and `<` to exclude them.

## Other ways

- `=IF(MEDIAN($F$1,B2,$F$2)=B2,"In range","Out of range")`: A compact trick: a value is between two limits exactly when it is their median. It also works when the limits are entered in either order.
- `=IF(ABS(B2-($F$1+$F$2)/2)<=($F$2-$F$1)/2,"In range","Out of range")`: Distance from the midpoint, handy when the range is a target plus or minus a tolerance.

## Common mistakes

- `=IF(18<=B2<=24,…)` doesn't work as in maths: Excel compares 18<=B2 first, gets TRUE or FALSE, then compares that with 24.
- Lock the limits with $ before filling down, or the references slide down to empty cells.

## Questions

**How do I count values between two numbers?**

Use COUNTIFS with two conditions on the same range, for example `=COUNTIFS(B2:B7,">="&F1,B2:B7,"<="&F2)`.

**How do I highlight values in a range?**

Use the same AND test as a conditional formatting rule: `=AND($B2>=$F$1,$B2<=$F$2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/if-between-two-numbers