While testing several conditions in Excel, sometimes, we may find the need to check whether a value lies between two items or not.
Despite having about 18 powerful Logical Functions, Excel lacks the BETWEEN function to accomplish this. So, if you’re looking for a BETWEEN function, there aren’t any right now!
But, Hang On! What if we create a custom formula that functions as the BETWEEN? Yes, you could nest IF and AND functions together to create a BETWEEN formula as a workaround. Then, we will use that formula to test conditions and return a Boolean Value like “Yes” and “No” or “True” and “False.”
Formula: IF(AND(value >= item1, value <= item2), “Yes”, “No”)Between for Numbers
To check if the number lies in between certain values, we will use logical operators like (>, <, =) in the formula.
Example: We have a simple list of numbers in Column D. Let’s assume, I want to check whether the numbers fall in between 50 and 60 or not.
To check, I entered the IF and AND formula as in the box. Then, I used Flash Fill to copy the formula for all of the remaining columns.
=IF(AND(D2>50,D2<=60),"TRUE","FALSE")
The formula resulted in TRUE in the first cell. Let’s see how we got this.
- AND(D2>50,D2<=60): First of all, the AND function tests two logical conditions. 55 >= 50 and 55 <= 60. Since both the arguments are correct, it results in TRUE.
- IF(AND(D2>50,D2<=60),”TRUE”,”FALSE”): Now, the IF function tests AND(D2>50,D2<=60) logical statement which is TRUE. Then, returns TRUE if the value is true and FALSE if not true. In our case, we got TRUE.
Between For Texts
Unlike Numbers, we cannot define one text string as greater or lesser to see if it falls in between two texts. But, we will use the A to Z Alphabetical order to find this out. This is especially helpful when you do not have sorted text strings in A to Z order.
Example: Suppose, I have a list of 3 names in Columns A, B, and C. Now, I need to see whether the 3rd Name lies in between the two names or not. For this, my formula would be:
=IF(AND(C5>=A5,C5<=B5),"Yes","No")
The formula resulted in Yes. Let’s take a look into how it tested and returned the output.
- AND(C5>=A5, C5<=B5): I have passed down two conditions to check in the AND function and return either a TRUE or FALSE boolean. First, if the value of Ina > = Harry. Next, the value of Ina < = Jack. Here, we’re checking if the alphabet I comes after H and J comes before I. We got TRUE for both conditions.
- IF(AND(C5>=A5,C5<=B5),”Yes”,”No”): Now, the IF function checks AND(C5>=A5,C5<=B5),”Yes”,”No”) condition which is TRUE. Then, it returns Yes when it’s True and No if the logical test is False. Here, we got Yes.
Between For Dates
There can be instances when you need to check if the date lies between the start date and the end date or not. Maybe, it could be within the next nth days or within the last nth days. So, this time for dates, what will be the factor here? Let’s find out through examples.
Example 1: Check Dates Between Start Date and End Date
Here, I have lists of Dates in Column D. I have the Start Date in cell G3 and the End Date in H3. Let’s find out whether the dates fall between January to August 2023 or not.
On the E3 cell, I entered the formula as
=IF(AND(D3>=$G$3, D3<=$H$3), "Yes", "No")
The formula returned “Yes” since both the tests (8/5/2023 > = 8/1/2023, 8/5/2023 < = 8/28/2023) returned TRUE.
Example 2: Check Date Within last Nth Days
Just like the start date and end date, you could also check whether the certain date falls within the last nth days. For Instance, last 4 days, 7 days, etc. Let’s quickly dive into the example.
Suppose, I have a list of Purchase items with Amount and Date. I want to check whether I’ve bought them within the last 4 days or not. Rather than manually looking at the calendar and counting it myself, I’ll simply use the formula.
But, to test this, you need Today’s Date. So, before you begin, enter =TODAY() in the cell to quickly return to the present date.
Now, you can compare your lists of dates with the present one in the formula. Enter the formula as
=IF(AND(I2>=L2- 4, I2<L2), "Yes", "No")
In the formula, we have subtracted the first condition in the AND function by 4. The AND function tests whether the Date in I2 is >= l2 – 4 or < L2. Then, it returned Yes.
Example 3: Check Date Within next Nth Days
When we subtract dates by a value, we get the past nth dates. Now, to check the next nth days, we will add the condition by a number.
Again, here, we will take the same data as example 2. But, we will test if the dates fall within the next 6 days. At first, we returned Today’s date in H2. Then, we entered the formula as:
=IF(AND(E2 >H2, E2<=H2+6), "Yes", "No")
As a result, we got “Yes” in the F2 cell. In the above formula, we’ve passed down the criteria in the AND function to check if the date of E2 > Today’s Date and E2<=Today’s Date + 6. The formula returned Yes since the condition was True.