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.

**NOTE:**Keep in Mind using the equals to “=” the operator in the formula is completely optional.

## 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.