Excel has close to 500 awesome formulas across various different categories, but it does not have a BETWEEN formula.
And there is probably a good reason for this – you can easily create your own between formulas by using a combination of different formulas such as IF, AND, NOT, etc.
So while it would be great to have a BETWEEN formula in Excel, there’s nothing you cannot do with a simple combination of existing functions.
In this excel tutorial, I will show you how you can create your own Between formulas in different scenarios.
BETWEEN Formula to Use with Numbers
One of the most common reasons people need something like a between formula is when they need to compare numbers and see whether a number lies in between the two given numbers.
Below I have a data set where I have the lower limit in column A in the upper limit in column B.
In column C, I have the value that I want to check whether it lies between the upper and the lower limit value.
There are multiple combinations of formulas that you can use to check for this in Excel.
Using the IF Function with Median Function
If you only need to check whether the value in column C is in between the lower and upper limit, then you can use a combination of the IF function and the MEDIAN function.
Below is the formula that checks whether the value in column C is in between the values in columns A and B or not. If it is, then it returns “In Range”, and if it is not, then it returns “Out of Range”
=IF(C2=MEDIAN(A2:C2),"In Range","Out of Range")
The logic here is very simple – the median function takes multiple values as inputs and returns the median value from that data set.
Since we only have three values here, the median function would return the value in column C, if it lies in between the values in columns A and B, else it would not return the value in column C.
With this logic, we use a simple if formula that checks whether the value in column C is equal to the median value returned by the formula or not.
If this condition is true, it returns “In Range”, and if this condition is false, then it returns “Out of Range”.
The drawback of this formula is that it will not be able to tell you whether the value in column C is below or above the threshold value.
It would only tell you whether it lies in between the two given values or not.
Also read: Excel If Statement with Multiple Conditions
Using IF + AND Functions
Another quick way to check whether a value lies between two given values is by using a combination of the IF and the AND function.
Below is the between formula that could return “In Range” if the value in column C is in between the value in column A and B, as it would return “Out of Range”.
=IF(AND(C2>A2,C2<B2),"In Range","Out of Range")
AND function works by checking all the conditions within it, and returns true if all the conditions are true, and returns false if any of the given conditions is false.
The If function then returns “In Range” if the result of the AND function is True, else it returns “Out of Range”
Using Nested IF Function to Create a Between Formula
If you would also like to check a given value is below or above the threshold value, then you can use a nested IF formula.
Below is the nested if formula that checks the value in column C with the values in columns A and B.
=IF(C2<A2,"Below Lower Limit",IF(C2>B2,"Above Upper Limit","In Between"))
If the value in cell C2 is less than the one in cell A2, it would return “Below Lower Limit”, or if it is more than the value in cell B2, then it would return “Above Upper Limit”.
And if none of these conditions is true, then it would return “In Between”
The benefit of this formula is that it gives us more information about the value in column C in case it is not in between the lower and upper limit values.
BETWEEN Formula to Use with Dates
Just like we created a between formula for numbers, you can also create one for dates. The formulas would exactly be the same as dates are stored as numbers in the back end in Excel.
So when you are comparing dates, you’re actually comparing numbers.
Below I have a data set where I have the start date in column A and the end date in column B, and I want to check the dates in column C and identify whether they lie in between these two dates or not.
Using the IF Function with Median Function
=IF(C2=MEDIAN(A2:C2),"In Between","Not Between Start and End Date")
The above formula would check whether the date is in between the start date and end date or not (using the MEDIAN function).
If it is, then it would return “In Between”, else it would return “Not Between Start and End Date”
Using Nested IF Function to Create a Between Formula
=IF(C2<A2,"Before Start Date",IF(C2>B2,"After End Date","In Between"))
The above Between formula created using nested if would check whether the date is before the Start date or after the End date.
It would return “Before Start Date” if the date in cell C2 is before the start date, and it would return “After End Date” if the date in cell C2 is after the end date.
if none of these conditions is true then it would return “In Between”
BETWEEN Formula to Use with Text
Not many people know this, but you can also check whether a text string is in between two given text strings or not.
For example, below I have two text strings in cells A2 and B2, and I can use a formula to check whether the text string in cell C2 is in between these two text strings or not.
=IF(AND(C2>A2,C2<B2),"In Between","Out of Range")
Excel compares these text strings alphabetically and gives the result based on it.
In this tutorial, I showed you how to create a between formula using a combination of simple formulas such as IF + Median or If +AND, or Nested IF.
I hope you found this Excel tutorial worthwhile.
Other Excel tutorials you may also find helpful:
- How to Calculate Hours between two Times in Excel?
- How to Compare Two Cells in Excel? (Exact/Partial Match)
- How to Compare Two Columns in Excel (using VLOOKUP & IF)
- Using IF Function with Dates in Excel (Easy Examples)
- Excel Logical Test Using Multiple If Statements in Excel [AND/OR]
- Multiple If Statements in Excel (Nested Ifs, AND/OR) with Examples
