IF Between Two Numbers in Excel

To check if a value falls between two numbers in Excel, use IF with AND: =IF(AND(B2>=1,B2<=5),"Standard","Other"). Excel has no BETWEEN function, so you write two tests, one for each limit, and AND makes sure both are true. This guide covers inclusive and exclusive limits, limits stored in cells, the MEDIAN shortcut, and how to sort values into several bands.

How to check if a number is between two values#

Write one test for the lower limit and one for the upper limit, and put both inside AND.

=IF(AND(B2>=1,B2<=5),"Standard","Other")

The examples use this parcel list in A1:B7. Parcels from 1 to 5 kg ship as Standard:

Fill the formula from C2 down to C7 (Windows: Ctrl+D; Mac: ⌘D). P2, P3 and P6 show Standard. P1 is too light, and P4 and P5 are too heavy, so they show Other.

AND returns TRUE only when every test inside it is true. The guide to IF with AND explains how it works in more detail, and the AND function guide is a quick reference.

Inclusive or exclusive: >= or >?#

>= and <= include the limits. > and < leave them out. P3 weighs exactly 5 kg, so the choice decides its result.

Words like "from 1 to 5", "at least" and "up to" mean the limit is included. "Over", "under" and "more than" mean it isn't. When bands meet, such as 0-5 and 5-10, include each limit in only one band so no value falls into both or neither.

Why 1<=B2<=5 doesn't work#

The maths-style chain looks right but gives wrong answers without any error. Excel reads =1<=B2<=5 from left to right: first it works out 1<=B2, which is TRUE or FALSE. Then it compares that TRUE or FALSE with 5.

In Excel, a logical value is always treated as greater than any number. So TRUE<=5 is FALSE, and FALSE<=5 is FALSE too. The formula says every parcel is outside the range, even P2. Always write two separate comparisons inside AND.

How to use limits stored in cells#

Put the lower limit in E1 and the upper limit in F1, then lock them with dollar signs:

=IF(AND(B2>=$E$1,B2<=$F$1),"Standard","Other")

Now you can change the band without touching the formula. Press F4 on Windows (⌘T on Mac) to add the $ signs while the cursor is in the reference.

If you're not sure which limit is bigger, for example when two people type them, let MIN and MAX sort it out:

=IF(AND(B2>=MIN($E$1,$F$1),B2<=MAX($E$1,$F$1)),"Inside","Outside")

This works whether E1 holds 1 and F1 holds 5, or the other way round.

Between two dates or times#

Dates and times are numbers in Excel, so the same pattern works. Build each limit with DATE rather than typing a date in quotes:

=IF(AND(B2>=DATE(2026,1,1),B2<=DATE(2026,3,31)),"Q1","")

This labels every date in the first quarter of 2026. A date typed as text, such as "31/03/2026", is compared as text, and the test quietly fails. If B2 also holds a time of day, an order placed at 3 pm on 31 March is slightly larger than DATE(2026,3,31), which means midnight. Use B2<DATE(2026,4,1) as the upper test to include the whole last day.

The MEDIAN shortcut#

A number is between two limits, inclusive, exactly when it's the middle value of the three. MEDIAN finds the middle value:

=IF(B2=MEDIAN(B2,1,5),"Standard","Other")

For P2, MEDIAN(2.5, 1, 5) is 2.5, which equals B2, so the result is Standard. For P4, MEDIAN(7.2, 1, 5) is 5, which doesn't equal 7.2, so it's Other. It's short and doesn't care about the order of the limits, but AND is easier for others to read. Use MEDIAN only for inclusive checks.

How to check if a number is outside a range#

Use OR with the opposite tests: below the lower limit or above the upper limit.

=IF(OR(B2<1,B2>5),"Outside","Inside")

P1, P4 and P5 show Outside. This is the mirror image of the AND version: notice that >= becomes < and <= becomes >. The guide to IF with OR covers OR in detail.

How to sort values into several bands#

When there are more than two outcomes, nest IFs from the smallest band up. Each test only needs the upper limit, because the earlier tests have already caught the smaller values.

=IF(B2<1,"Small",IF(B2<=5,"Standard",IF(B2<=10,"Large","Freight")))

P1 is Small, P2, P3 and P6 are Standard, P4 is Large and P5 is Freight. In Excel 2019 and later, IFS reads more easily:

=IFS(B2<1,"Small",B2<=5,"Standard",B2<=10,"Large",TRUE,"Freight")

For many bands, such as tax brackets or grade boundaries, a lookup table with an approximate match is easier to maintain than a long formula. For partial text checks instead of numbers, see IF with text.

How to count or sum values between two numbers#

IF checks one row at a time. To count all the values in a range, use COUNTIFS with two conditions on the same column:

=COUNTIFS(B2:B7,">=1",B2:B7,"<=5")

The result is 3. SUMIFS works the same way for totals. The guide to COUNTIF between two numbers explains the quotes and operators, and the IF function explained simply covers the basics of IF. The IF function guide has the full syntax.

Practise it#

Between checks are a staple of Excel tests because they combine IF, AND and careful operators. Practise AND, OR and NOT in a live spreadsheet: you type the formula and it checks it instantly. Then practise the IF function, and try the intermediate Excel test when you're ready.