It’s easy to check whether test results fall within their limits in Google Sheets. You can use either the ISBETWEEN function or comparison operators. This tutorial will walk you through both approaches.
In such logical tests, typically three arrays are involved:
- Actual results – the values to test.
- Lower limit – start of the acceptable range.
- Upper limit – end of the acceptable range.
For example, in a medical lab report, the Result column corresponds to the test results, while Range From and Range To columns represent the normal limits.
Similarly, in a job schedule, you might want to test whether actual start dates fall within scheduled start and end dates.
Example Dataset
Here, we want to check if each Result falls between its corresponding Range From and Range To.
Solution 1: Using ISBETWEEN
Place this formula in cell F2:
=ISBETWEEN(B2:B5, C2:C5, D2:D5)- B2:B5 → values to compare.
- C2:C5 → lower limits.
- D2:D5 → upper limits.
This will return TRUE for values within range and FALSE otherwise.
To handle dynamic ranges with possible blank rows, use:
=ARRAYFORMULA(IF(LEN(B2:B), ISBETWEEN(B2:B, C2:C, D2:D), ))This ensures the formula only evaluates rows with actual data.
Solution 2: Using Comparison Operators
You can achieve the same result using comparison operators:
=ARRAYFORMULA(IF((B2:B5>=C2:C5)*(B2:B5<=D2:D5), TRUE, FALSE))Or, for dynamic ranges:
=ARRAYFORMULA(IF(LEN(B2:B), IF((B2:B>=C2:C)*(B2:B<=D2:D), TRUE, FALSE), ))Both formulas return TRUE if the result falls within the limits, FALSE otherwise.
Why ISBETWEEN is Recommended:
- Simpler to read and learn.
- Automatically returns
#NUM!if the lower limit is greater than the upper limit, which helps catch data errors.
Highlight Out-of-Range Test Results
To highlight values outside their limits using conditional formatting:
- Select cell B2.
- Go to Format → Conditional formatting.
- Set Apply to range:
B2:B1000. - Under Format rules, choose Custom formula is.
- Enter:
=ISBETWEEN($B2, $C2, $D2)=FALSE- Set the formatting style (e.g., red text, bold).
- Click Done.
This highlights all results that do not fall within their range.
Key Takeaways
- Use ISBETWEEN to easily test if values fall within a range.
- Comparison operators provide an alternative, but formulas are more complex.
- ISBETWEEN works with numbers, dates, or text, and handles multiple arrays.
- Conditional formatting can flag out-of-range values visually.
