Exceljet

Quick, clean, and to the point

Value is between two numbers

Excel formula: Value is between two numbers
Generic formula 
=AND(val>MIN(num1,num2),val<MAX(num1,num2))
Explanation 

To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is:

=AND(D5>MIN(B5,C5),D5<MAX(B5,C5))

How this formula works

At the core, this formula runs two tests on a value like this:

=D5>MIN(B5,C5) // is D5 greater than smaller?
=D5<MAX(B5,C5)) // is D5 less than larger?

In the first expression, the value is compared to the smaller of the two numbers, determined by the MIN function.

In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function.

The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number.

Include boundaries

To include the boundary numbers (num1 and num2), just adjust the logic like so:

=AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))

Now the AND function will return TRUE only when the value is greater than or equal to the smaller number and less than or equal to the larger number.

Simple version

The formula in the example is more complex because there is no assumption that num1 is less than num2 (or vice versa).

If it's safe to assume that num1 is less than num2, the formula can be simplified:

=AND(value>num1,val<num2))
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.