It is easy to find whether test results fall within their limit in Google Sheets. You can use one relatively new built-in function or the comparison operators for this purpose. I will walk you through both options.

In this type of logical test, there will possibly be three arrays involved.

The first array will contain the actual test results. The other two ranges/arrays will contain their corresponding start and end limits or parameters.

If you relate the above to a medical lab report, we can consider the result column and normal range (range from and to) columns.

See the below table, which I have prepared in Google Sheets.

I have test results in column B (array 1) and their normal range in columns C (array 2) and D (array 3).

Please note that it’s just sample data and not a realistic one.

In this table, I want to find whether the blood test results fall within their limit/range.

Let’s consider another example. You have a job schedule in hand, and you want to test whether the actual start dates fall within their scheduled start and end dates.

There will be a minimum of four columns – job description, scheduled start date, scheduled completion date, and actual start date. We require the last three columns.

If you follow the above two scenarios, you can understand that you want to compare values in one array/range with the values in two other arrays/ranges.

Let me walk you through how to do that using the Isbetween (yes, it’s the function that serves our purpose) function or the alternative comparison operators in Google Sheets.

Formula to Test Whether Results Fall within Their Limit

ISBETWEEN Formula (Solution 1)

If we consider the table shown above, we can use the below ISBETWEEN formula in cell F2.

=isbetween(B2:B5,C2:C5,D2:D5)

It is the easiest way to find whether test results fall within their given limits.

There are three arrays used in this formula. They are B2:B5 (values to compare), C2:C5 (lower values), and D2:D5 (upper values). It expands to the range F2:F5.

But when you want to cover several rows that often contain blank rows, better limit the expansion to rows, which has values in the “Test Result” column. The below Isbetween in F2 can fulfill that requirement.

=ArrayFormula(if(len(B2:B),isbetween(B2:B,C2:C,D2:D),))

Here I have included three additional Google Sheets functions. They are ARRAYFORMULA, IF, and LEN.

Operator Based Formula (Solution 2)

We can compare one array with two other arrays, which we have already done above, in Google Sheets using the comparison operators too.

To find whether results fall within their limit, other than the Isbetween above, in Google Sheets, we can use the following operator-based formula.

=ArrayFormula(if((B2:B5>=C2:C5)*(B2:B5<=D2:D5),TRUE,FALSE))

Similar to the Isbetween formula above, we can make the range reference here also open, with the help of the additional three functions. It is as follows.

=ArrayFormula(IF(LEN(B2:B),if((B2:B>=C2:C)*(B2:B<=D2:D),TRUE,FALSE),))

Comparison of Outputs

You can use either of the above formulas to test whether results fall within their limit/range in Google Sheets.

I prefer the Isbetween to the operator-based one as the former is a lot easier to learn.

There is one more reason for my recommendation. What’s that?

There are three arrays involved in the test – Result, Range Lower Limit, and Range Upper Limit.

We usually expect the values in the Range Upper Limit greater than or equal to the Range Lower Limit.

If that doesn’t meet, the Isbetween is capable of identifying the same. It would return the #NUM error in that case.

Highlight Test Results That Does Not Fall within Their Given Limits

Here I am not highlighting the cells instead highlighting the texts/values. I hope that would be preferable.

Steps:

1. Go to cell B2 to make it the active cell.
2. Go to the menu Format and click Conditional formatting.
3. Replace B2 in the “Apply to range” field with B2:B1000.
4. Under format values, select “Custom formula is” from the drop-down menu.
5. Paste the formula =isbetween(B2,C2,D2)=false in the given field.
6. Below that, you can find “Formatting styles”. Change text color to red, then bold it, and set the fill color to none.
7. Click “Done”.

This way, we can highlight test results that don’t fall within their limit in Google Sheets.

These are the different options to test whether results are falling within their set limit in Google Sheets.

Thanks for the stay. Enjoy!