This guide will explain **how to use BETWEEN formula in Excel using three easy and simple methods**.

##### Table of Contents

Excel is a popular tool containing hundreds of built-in formulas to make calculations and other tasks easy. However, Excel does not have any built-in `BETWEEN`

formulas.

And this is not something to fret about. Since Excel already has several built-in formulas, we can use a few of them to create our own `BETWEEN`

formula easily.

The `BETWEEN`

formula is used to compare values to other ranges of values to determine whether the value is between them. So we want to determine if a value is between two other values in a given data set.

Moreover, the value does not have to be a number. But, it can also identify if a date or text is between two given values in our data set.

Let’s take a sample scenario wherein we need to use a `BETWEEN`

formula in Excel.

Suppose you are making a sales report. And you want to compare the 2022 sales to the 2020 and 2021 sales. Furthermore, you want to determine if the 2022 sales are between the total sales for 2021 and 2020.

Since Excel does not have an explicit `BETWEEN`

formula, you opted to create one using the `AND`

function, `MIN`

function, and `MAX`

function.

Great! Now let’s move on and learn how to use a `BETWEEN`

formula in Excel using three easy and simple methods.

**How to Use BETWEEN Formula in Excel for Numbers**

Firstly, let’s discuss how to use a `BETWEEN`

formula in Excel specifically for numbers. Moreover, one of the most common uses of the `BETWEEN`

formula is to compare a number between two given numbers to determine whether the value is between the two numbers.

For instance, we have a list of numerical values arranged by the lower limit and upper limit. Then, we have the value we want to determine is between the lower and upper limits in another column.

Luckily, there are many combinations of formulas we can use to create a `BETWEEN`

formula for numbers in Excel. In this case, we will use a combination of the `IF`

function and `MEDIAN`

function to determine whether the values are between the lower and upper limits.

Another combination we can utilize is the combination of the `IF`

function and the `AND`

function. And both combinations will return TRUE or we can specify what value it will return if the value is between the two given limits or FALSE if it is not between the two limits.

To use this method, follow the step-by-step process below:

1. Firstly, we will input the result in a new column. In this case, we will first try the combination of the `IF`

function and the `MEDIAN`

function. So type in the formula “**=IF(D3=MEDIAN(B3:D3), “TRUE”, “FALSE”)**”. Then, press the **Enter **key to return the result.

2. Secondly, we can drag down the fill handle to apply the formula to the other cells.

3. And tada! We have used the `BETWEEN`

formula in Excel.

4. Furthermore, let’s try the `IF`

function and `AND`

function combination. So we will input the formula “**=IF(AND(D3>B3,D3<C3),”TRUE”,”FALSE”)**”. Then, press the **Enter **key to return the result.

5. Next, we can drag down to copy the formula to the other cells.

6. And tada! We have used another `BETWEEN`

formula in Excel.

**How to Use BETWEEN Formula in Excel for Dates**

Next, we will discuss how to create a `BETWEEN`

formula in Excel to use for dates. For example, we want to determine if a specific date is between two given dates. Since Excel sees dates as numbers, the formula will be the same. When we compare dates, we are essentially comparing numbers still.

For example, we have a data set containing the start and end dates. Then, we have a column of specific dates to check if it is between the start and end dates.

Since dates are exactly the same as numbers in Excel, we can use the same combinations of formulas. Firstly, we can use a combination of the `IF`

function and the `MEDIAN`

function.

Next, we can use a nested `IF`

function to create another `BETWEEN`

formula. In this case, this formula would check if the specific date is before the start date or after the end date. So it will either return “before start date” or “after start date.”

To use this method, follow the step-by-step process below:

1. Firstly, we will use the `IF`

function and `MEDIAN`

function combination. So type in the formula “**=IF(D8=MEDIAN(B8:D8),”TRUE”,”FALSE”)**”. Then, press the **Enter **key to return the result.

2. Next, we can drag down the fill handle to copy the formula to the other cells.

3. And tada! We have determined whether the date was between the start date and the end date.

4. After, let’s try using the nested `IF`

formula. So input the formula **“=IF(D8<B8,”BEFORE START DATE”,IF(D8>C8,”AFTER START DATE”,”IN BETWEEN”))**”. Lastly, press the **Enter **key to return the result.

5. Then, we can drag down the fill handle to apply the formula to the other cells.

6. And tada! We have successfully used the nested `IF`

formula as a `BETWEEN`

formula.

**How to Use BETWEEN Formula in Excel for Text**

Lastly, we can also create a `BETWEEN`

formula in Excel specifically to deal with text. And this is something not everyone knows or is common to do. But, we can determine whether a text string is between two given text strings.

For example, we have a data set containing two columns of text. Then, we have another column containing the text we want to check is between the first two text strings.

How does this work? The formula will determine the text strings alphabetically and return a result based on that. Similarly, we can use the same combination of formulas to perform this task.

In this case, we will use a nested `IF`

formula to check whether a text string is between two given text strings.

Let’s learn how to use this method by following the process below.

1. Firstly, we will input the formula “**=IF(AND(D13>B13,D13<C13),”TRUE”,”FALSE”)**”. Then, press the **Enter **key to return the result.

2. Next, we can drag down the fill handle to copy the formula and apply it to the other cells.

3. And tada! We have successfully used a `BETWEEN`

formula in Excel for text strings.

You can make your own copy of the spreadsheet above using the link attached below.

And that’s pretty much it! We have explained how to use `BETWEEN`

formula in Excel using three easy and simple methods. Specifically, we discussed how to create a `BETWEEN`

formula in Excel for numbers, dates, and even text strings.

Are you interested in learning more about what Excel can do? You can now use the `MEDIAN`

function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.