How to check if a value is between two numbers in Excel

IF with AND: the standard formula

The sheet is a shipping log. Parcels from 2 to 10 kg go at the standard rate, so each row needs a Yes or No. A value is between two numbers when it passes two tests at once: greater than or equal to the lower limit, and less than or equal to the upper limit. AND checks both, and IF turns the result into a label.

The limits live in G1 and G2 with dollar signs, so the formula can be filled down column D without the references sliding. Change G2 to 12 and every row updates.

Inclusive or exclusive limits

>= and <= include the limits, so a parcel of exactly 2 kg counts. Switch to > and < when the limits themselves should not count. Row 3 is exactly 2 kg, which shows the difference: inclusive says Yes, exclusive says No.

When bands sit back to back (2-10 kg, then 10-20 kg), make one end inclusive and the other exclusive, such as >= the lower limit and < the upper. Otherwise a 10 kg parcel lands in both bands.

Why =IF(2<=B4<=10, ...) gives the wrong answer

It reads like maths, but Excel works left to right. 2<=B4 becomes TRUE, then Excel compares TRUE<=10. In Excel comparisons, TRUE and FALSE rank above every number, so that is FALSE, and the formula says No for every parcel. There is no error to warn you. Always split the test into two parts inside AND.

Between two dates (and the time-of-day trap)

Dates are numbers in Excel, so the same AND test works. Keep the start and end dates in cells (G3 and G4 here), or build them with DATE(2026,11,20). Do not type a date in quotes, as in C6<="11/30/2026": that is text, not a date, and because Excel ranks text above every number, the comparison is TRUE for any date.

Row 6 shipped on November 30, the last promo day, but at 16:45. A date with a time is bigger than the plain date, so C6<=$G$4 is FALSE and =IF(AND(C6>=$G$3,C6<=$G$4),"Promo","Regular") says Regular. Test against the start of the next day instead: less than the end date plus 1.

The MEDIAN trick: limits in either order

If people might type the limits the wrong way round (10 in G1, 2 in G2), the AND formula silently says No to everything. MEDIAN of three numbers returns the middle one, so a value is between the limits exactly when it is the median of itself and the two limits, whichever order they are in.

It is always inclusive, and MEDIAN skips empty and text cells. If a limit cell is blank, MEDIAN works on two numbers and the answer is quietly wrong, so only use it when both limits are filled. The plain alternative is =IF(AND(B4>=MIN($G$1,$G$2),B4<=MAX($G$1,$G$2)),"Yes","No").

Blank cells and numbers stored as text

Row 8 has no weight. An empty cell counts as 0, so with limits of 2 and 10 it says No, but with a lower limit of 0 it would say Yes. Skip empty rows first with =IF(B8="","",IF(AND(B8>=$G$1,B8<=$G$2),"Yes","No")).

Row 7 holds 7.5 imported as text. Excel ranks text above every number in comparisons, so B7>=2 is TRUE but B7<=10 is FALSE, and a 7.5 kg parcel gets No. Convert the column to real numbers (the numbers stored as text guide shows how) or wrap the cell in VALUE().

Labels for several bands, and the whole column at once

For more than two outcomes, test the bands in order with IFS (Excel 2019 and later) so each test only needs its upper limit: the first true test wins. With many bands, a small lookup table with each band's lower limit and VLOOKUP(B2,table,2,TRUE) is easier to maintain.

In Excel 365 you can test the whole column in one spilled formula, but not with AND, because AND collapses every row into a single TRUE or FALSE. Multiply the tests instead: =IF((B2:B8>=G1)*(B2:B8<=G2),"Yes","No"). To count the rows in range rather than label them, use COUNTIFS with two conditions.