IF.BETWEEN.B Function in Excel

The IF.BETWEEN.B function is a unique feature introduced by Modern Functions Update (MFU) that does not exist in any version of vanilla Excel. You can now run it for free on your Excel from 2007 onward, including 365 and 2024 versions.

---

Description

The IF.BETWEEN.B function checks whether a value falls between two boundaries and returns a simple TRUE or FALSE. It is a simpler version of IF.BETWEEN that only returns a logical value. The order of the boundaries does not matter.

This is particularly useful for:

• Logical tests: Quickly check if values fall within a range
• Conditional formatting: Create rules based on value ranges
• Data validation: Flag values that are outside acceptable limits
• Filtering: Create helper columns for filtering data
• Nested formulas: Use as a building block for more complex conditions

---

Syntax

=IF.BETWEEN.B(value, boundary1, boundary2, [exclusive])

Argument	Description
value	Required. The value to test.
boundary1	Required. The first boundary value.
boundary2	Required. The second boundary value.
[exclusive]	Optional. Specifies whether boundaries are included. 0 (default) = inclusive, 1 = exclusive.

---

Important Notes

• The order of boundaries does not matter – the function automatically determines the lower and upper bounds.
• Inclusive (default) means the boundaries are included in the range: boundary1 <= value <= boundary2.
• Exclusive means the boundaries are excluded from the range: boundary1 < value < boundary2.
• The function always returns TRUE or FALSE – no other values.

---

Examples

Basic Usage – Inclusive (Default)

To check if a value is between 10 and 20 (including 10 and 20):

=IF.BETWEEN.B(15, 10, 20)

Returns: TRUE

=IF.BETWEEN.B(10, 10, 20)

Returns: TRUE (inclusive)

=IF.BETWEEN.B(20, 10, 20)

Returns: TRUE (inclusive)

=IF.BETWEEN.B(5, 10, 20)

Returns: FALSE

Exclusive Boundaries

To check if a value is strictly between 10 and 20 (excluding 10 and 20):

=IF.BETWEEN.B(15, 10, 20, 1)

Returns: TRUE

=IF.BETWEEN.B(10, 10, 20, 1)

Returns: FALSE (10 is excluded)

=IF.BETWEEN.B(20, 10, 20, 1)

Returns: FALSE (20 is excluded)

Using Cell References

If A2 contains the value, B2 and C2 contain boundaries:

=IF.BETWEEN.B(A2, B2, C2)

With Dates

Check if a date falls within a range:

=IF.BETWEEN.B(TODAY(), DATE(2024,1,1), DATE(2024,12,31))

With Text Values

Check if a text string falls alphabetically between two values:

=IF.BETWEEN.B("M", "A", "Z")

Returns: TRUE

Boundary Order Examples

The order of boundaries does not matter:

=IF.BETWEEN.B(15, 10, 20)
=IF.BETWEEN.B(15, 20, 10)

Both return the same result (TRUE).

---

IF.BETWEEN.B vs IF.BETWEEN

Feature	IF.BETWEEN.B	IF.BETWEEN
Return value	Only TRUE or FALSE	Any value you specify
Custom return values	❌ No	✅ Yes
Arguments	3 or 4	5 or 6
Complexity	Simpler	More flexible
Use case	Quick logical tests	Conditional results

---

Practical Use Cases

Scenario	Formula
Check if value is in range	=IF.BETWEEN.B(A2, B2, C2)
Conditional formatting rule	=IF.BETWEEN.B(A2, 10, 20, 1)
Date range validation	=IF.BETWEEN.B(Date, Start, End)
Quality check helper column	=IF.BETWEEN.B(Value, LSL, USL, 1)
Price range check	=IF.BETWEEN.B(Price, 50, 100)

---

Common Problems

Problem	What It Means	What To Do
#NAME? error	Excel doesn’t recognize IF.BETWEEN.B	Install Modern Functions Update
#VALUE! error	Non-numeric boundaries with numeric value	Ensure boundaries match the data type
Returns FALSE unexpectedly	Value not between boundaries	Check inclusive/exclusive setting

---

Notes

• ✅ Fully tested – The implementation in Modern Functions Update works reliably.
• ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
• ℹ️ IF.BETWEEN.B is a unique MFU feature not found in any Excel version.
• ℹ️ The order of boundaries does not matter – the function automatically sorts them.
• ℹ️ Inclusive is the default behavior (exclusive = 0).
• ℹ️ This function is simpler and faster for logical tests.

---

See Also

IF.BETWEEN – Extended version with custom return values