A little while ago Brendan asked me how he could check if values in a list fall between two limits.
Now, it’d be nice if there was a BETWEEN Function, but there isn’t. Hence why the title of this tutorial isn’t ‘BETWEEN Function’, it’s ‘BETWEEN Formula’.
I’ve got two options for you.
Watch the Video
Enter your email address below to download the sample workbook.
Excel BETWEEN formula using AND
In the example table below I’m checking if the value/date in column B falls within and inclusive of the upper and lower limits:
In English, the formula in cell E5 simply reads: is the value in B5 greater than or equal to the value in cell C5 (the lower limit) AND less than or equal to the value in cell D5 (the upper limit)?
If you strictly want to check if a value/date falls between the upper and lower limits, you can omit the equals sign from the formulas like so:
Excel BETWEEN formula using MEDIAN (Inclusive)
The MEDIAN function returns the middle value in a set of 3 numbers. If the value is between two values, then it will be returned by MEDIAN, which makes it perfect for this task.
Taking the examples above, the formula in English reads: does the value in B23 equal the median of the value, lower limit, and upper limit? It’s inclusive of the upper and lower limits, as we can see by the results on rows 24 and 29.
If you prefer to exclude the upper and lower limits, you can add/subtract 1 to the lower and upper limits respectively, as shown below:
Thanks to Roberto for the MEDIAN solution.