There is no explicit **Between formula** in Excel, however we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?

We have three possible scenarios: **numbers, dates, and text**.

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

**STEP 1:** For **numbers**, we have a creative use of the **Median formula**:

## =IF(C7=MEDIAN(A7:C7), “Yes”, “No”)

In our first example above, the range is 20-60, upon checking the value 50, it is in between this range.

The median formula will return the value in the middle of these 3 values when arranged in increasing order: 20, 50, 60. The median value is 50. Since it matches the value we are evaluating, then the answer we get is a **Yes, **this value (50) is in between the range.

**STEP 2:** For **dates, **we have the same application of the **median formula. **Because Excel treats dates as numbers too:

## =IF(C10=MEDIAN(A10:C10), “Yes”, “No”)

In our first example above, the range is May 1 – July 1, upon checking the date June 1, it is in between this range.

The median formula will return the value in the middle of these 3 dates when arranged in increasing order: May 1, June 1, July 1. The median value is June 1. Since it matches the value we are evaluating, then the answer we get is a **Yes, **this value (June 1) is in between the range.

**STEP 3:** For text, we are checking if the value is alphabetically in the middle. We will be using the **and formula:**

## =IF(AND(C12>=A12, C12<=B12, “Yes”, “No”)

Interestingly enough, you can compare texts using the **>= and <= operators. **Excel is able to compare them which goes alphabetically first or last.

In our first example above, the range is Cat – Dog, upon checking the text Cow, it is in between this range. As when arranged alphabetically, it would be: Cat, Cow, Dog.

The And formula checks if **Cow >= Cat, **and **Cow <= Dog**. You will see that both of these are true, as **Cow **is alphabetically later than **Cat**, while **Cow **is alphabetically ahead of **Dog**. Which is why we get a **Yes **result.

HELPFUL RESOURCE: