How to Use BETWEEN Formula in Excel

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

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.Use BETWEEN Formula in Excel

 

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

 

3. And tada! We have used the BETWEEN formula in Excel.Final output

 

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.Use BETWEEN Formula in Excel

 

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

 

6. And tada! We have used another BETWEEN formula in Excel.Final result

 

 

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.Use BETWEEN Formula in Excel

 

2. Next, we can drag down the fill handle to copy the formula to the other cells.Use BETWEEN Formula in Excel

 

3. And tada! We have determined whether the date was between the start date and the end date.Use BETWEEN Formula in Excel

 

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.Use BETWEEN Formula in Excel

 

5. Then, we can drag down the fill handle to apply the formula to the other cells.Use BETWEEN Formula in Excel

 

6. And tada! We have successfully used the nested IF formula as a BETWEEN formula.Use BETWEEN Formula in Excel

 

 

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.Use BETWEEN Formula in Excel

 

2. Next, we can drag down the fill handle to copy the formula and apply it to the other cells.Use BETWEEN Formula in Excel

 

3. And tada! We have successfully used a BETWEEN formula in Excel for text strings.Use BETWEEN Formula in Excel

 

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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like