Test whether a value falls between two numbers
Check whether a value sits inside a lower and upper bound and return one result if it does, another if it doesn't, with AND nested inside IF.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →AND only returns TRUE when both comparisons hold, which is exactly what "between two bounds" means — a value has to clear the lower bound and stay under the upper bound at the same time, not either one alone.
- →Using >= and <= makes both endpoints inclusive; switching either one to a strict > or < excludes that edge from counting as in range.
- →The same shape extends past two conditions — stack additional comparisons inside the same AND to test a value against three or more bounds at once.
The fine print
- →A blank cell used as value compares as 0 in Excel's numeric comparisons, which can quietly count as in range against a lower_bound below zero — worth checking for blanks separately if that matters.
- →lower_bound and upper_bound need to be actual numbers, or numeric cell references, not text that merely looks like a number, or the comparison fails without an obvious error.
- →Works identically in Excel and Google Sheets.
Part by part
- =IF(AND(
- AND requires every condition to be true. It is necessary because spreadsheet formulas cannot chain comparisons the way some programming languages can.
- {{value}}>={{lower_bound}}
- The lower test, inclusive. Whether the bounds are inclusive is a decision worth making deliberately rather than inheriting from a copied formula.
- {{value}}<={{upper_bound}}
- The upper test. Both use the value, which is why it appears twice — there is no shorthand for a range comparison.
Common mistakes
=IF(10<=A2<=20, "Yes", "No")Chained comparison is not valid here. It evaluates left to right, producing TRUE or FALSE, then compares that to 20 — so the result is almost always TRUE regardless of the value.
=IF(AND(A2>=10, A2<=20), "Yes", "No")=IF(OR(A2>=10, A2<=20), "Yes", "No")OR instead of AND makes the test true for every number, since any value satisfies at least one of the two conditions. It returns Yes for everything and looks like it is working.
=IF(AND(A2>10, A2<20), "Yes", "No")Strict comparisons exclude the boundaries, so exactly 10 and exactly 20 return No. Not wrong if intended, and a frequent off-by-one when it is not.
Questions
Why can I not write 10 <= A2 <= 20?
Because the formula language has no chained comparison. The first comparison produces a boolean, which is then compared to the second bound — and TRUE is treated as 1, so the outcome is not what the notation suggests.
How do I test several ranges at once?
Nest IFs from the narrowest band outward, or use IFS in current versions, which takes condition and result pairs and reads far better. For many bands, a lookup table with approximate MATCH beats both.
Does it work with dates?
Yes, since dates are numbers underneath. Reference date cells rather than typing dates as text, which compares alphabetically and produces silently wrong results.
Can I return a number instead of text?
Yes, any value works in either branch. Returning a number keeps the result usable in later arithmetic, whereas text has to be converted back — worth choosing deliberately at the point you write it.