If the value is in A2, the lower limit in B2, and the upper limit in C2, use =AND(A2>=B2,A2<=C2) to check whether it falls between the limits, including both endpoints. Excel returns TRUE when it does and FALSE when it does not.
The Basic Between-Range Formula
The formula combines two comparisons with AND: the value must be greater than or equal to the lower limit, and it must also be less than or equal to the upper limit. Both comparisons have to be true for the overall result to be TRUE. In this example, A2 is the value being tested, B2 is the minimum allowed value, and C2 is the maximum.
For example, if A2 contains 12, B2 contains 10, and C2 contains 20, the formula returns TRUE. If A2 contains 25, it returns FALSE because 25 is above the upper limit. You can enter the formula in another cell, such as D2, then copy it down to check additional rows. As you fill it down, Excel adjusts the relative references to A3, B3, and C3, and then to the corresponding cells on later rows.
The formula returns a logical value, not a word or a number. TRUE and FALSE can be used directly in other formulas, filters, or conditional-formatting rules. If you want a more readable result for a report, use an IF formula instead; that option is covered below.
Inclusive and Exclusive Limits
The greater-than-or-equal-to and less-than-or-equal-to operators make the basic formula inclusive: a value exactly equal to either limit counts as being in range. That is often what people mean by “between,” such as when checking whether a score meets a minimum and maximum or whether a measurement falls within an allowed interval.
If the endpoints should not count, use strict comparisons instead. With the value in A2, the lower limit in B2, and the upper limit in C2, enter =AND(A2>B2,A2<C2). This returns TRUE only when A2 is greater than B2 and less than C2. For limits of 10 and 20, a value of 10 or 20 returns FALSE, while 12 returns TRUE.
Choose the operators according to the rule you are modeling, rather than assuming one interpretation of “between.” For example, a policy that accepts scores from 60 through 80 usually intends to include both 60 and 80. A test for values strictly inside those boundaries would exclude the exact endpoints. The difference is just the equals sign, but it can change results in boundary cases.
When the Two Limits May Be in Either Order
The basic formula assumes B2 is the lower limit and C2 is the upper limit. If people may enter the limits in either order, the basic version can return FALSE even when the value lies between the two numbers. For example, a value of 12 is between 10 and 20, but not between 20 and 10 when the formula treats the first limit as the minimum.
To make the formula handle either order, calculate the smaller and larger limit inside the comparisons. With the value in A2 and the two limits in B2 and C2, use =AND(A2>=MIN(B2,C2),A2<=MAX(B2,C2)). MIN returns whichever limit is smaller, and MAX returns whichever is larger. If B2 is 20 and C2 is 10, the formula still checks whether A2 is at least 10 and no more than 20.
This version is useful for user-entered thresholds, slider-like inputs, or worksheets where the order is not controlled. If the limits are supposed to be entered in a particular order, however, it may be better to flag reversed inputs rather than silently accommodate them. That can help users spot a data-entry mistake.
Returning a Label Instead of TRUE or FALSE
A logical result is convenient for calculations, but labels can be easier to scan in a worksheet. With the tested value in A2, the lower limit in B2, and the upper limit in C2, use =IF(AND(A2>=B2,A2<=C2),"In range","Out of range"). When A2 is 12 and the limits are 10 and 20, the result is In range; when A2 is 25, the result is Out of range.
IF checks the AND condition and returns its second argument when the condition is TRUE, or its third argument when it is FALSE. You can replace those labels with other text, such as “Pass” and “Review,” provided the wording matches the rule. The comparison logic remains the same.
For a numeric result, you could return values such as 1 and 0 instead of labels. That can be helpful when a later calculation needs to count or sum qualifying rows. Keep in mind that the values should consistently mean the same thing throughout the workbook; otherwise, a numeric flag may be less clear to someone reviewing the sheet.
Highlight Values Between Limits
To highlight qualifying values without adding a TRUE/FALSE helper column, use conditional formatting. Suppose the values to check are in A2:A100, and the shared lower and upper limits are in B1 and C1. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A2>=$B$1,A2<=$C$1), choose a format, and confirm the rule.
The A2 reference is relative, so Excel evaluates the rule against each cell in the selected range: A2 for the first row, A3 for the next, and so on. The dollar signs in $B$1 and $C$1 keep the limits fixed as the rule is applied to other rows. If the selected range starts in a different row or column, adjust the relative reference to match its top-left cell.
On Excel for Microsoft 365 on Windows, those are the ribbon commands for creating a formula-based rule. The Mac interface also has conditional formatting and formula-based rules, though the dialog layout may differ. Excel for the web supports conditional formatting as well, but available rule-editing details can vary; if you cannot find the formula-based option there, open the workbook in the desktop app.
Use a fill or font style that remains legible with the worksheet’s existing formatting. Conditional formatting changes how cells look; it does not alter their underlying values. You can edit or remove the rule through Home > Conditional Formatting > Manage Rules.
Counting How Many Values Fall in a Range
If you need a total rather than a row-by-row result, COUNTIFS can count values that meet both bounds. Suppose the values are in A2:A8, the lower limit is in B1, and the upper limit is in C1. Enter =COUNTIFS(A2:A8,">="&$B$1,A2:A8,"<="&$C$1). The criteria join each comparison operator to the relevant limit, while the dollar signs keep those limit references fixed.
For example, if A2:A8 contains 4, 8, 10, 12, 15, 20, and 25, while B1 is 10 and C1 is 20, COUNTIFS returns 4: the qualifying values are 10, 12, 15, and 20. This count includes both endpoints. To make it exclusive, change the criteria operators to > and <.
COUNTIFS is useful for summaries, dashboards, and quality checks where you want a single count of matching records. It counts numeric values in the specified range that satisfy the criteria; it does not identify which rows matched. Use a row-level AND formula or conditional formatting when you need to inspect individual values.
Numbers, Dates, Blanks, and Precision
Excel can compare dates and times with the same logic because it stores them as numeric serial values. If A2 contains a date and B2 and C2 contain start and end dates, the between formula can test whether the date falls within that interval. Apply a date format to the result cells if needed, but the comparison itself does not require a special date function.
Time limits work similarly. A time from 9:00 AM through 5:00 PM can be checked with inclusive comparisons, provided the cells contain actual Excel time values. If the data also includes dates, compare full date-time values when the date matters; checking only the displayed time can lead to a result that overlooks which day the entry belongs to.
Check whether imported numbers are actually numeric. A value that looks like 12 may be stored as text, especially after copying data from another system. Text values can behave differently in comparisons and may prevent a test from producing the result you expect. Converting the source cells to numbers, or fixing the import, is usually safer than building a complicated workaround into every formula.
Also consider blanks and rounding. A blank input should not automatically be treated as a meaningful measurement just because a comparison produces a result. If blank values are possible, add a separate completeness check or validate the input cells. For decimal values, a displayed 10.00 may contain more precision than is shown; formatting changes the display, not the stored value. If the business rule requires rounding, decide the required precision explicitly rather than assuming the displayed digits are the entire value.
Common Mistakes to Check
- Using OR instead of AND: For an ordinary interval, the value must satisfy both limits. OR would accept a value above the lower limit or below the upper limit, which can make values outside the intended range pass.
- Reversing the comparisons: The lower-limit test must compare the value as greater than the minimum, and the upper-limit test must compare it as less than the maximum.
- Forgetting whether endpoints count: Decide whether equality should pass, then use either inclusive or strict operators consistently.
- Copying a formula with the wrong references: Relative references move when copied. Use absolute references for shared limits, such as $B$1 and $C$1, when appropriate.
- Comparing text to numbers: Check the cell contents and number format, especially for data pasted from external sources. A number format alone does not necessarily convert text into a numeric value.
When a result looks wrong, test one known value below the range, one exactly at each boundary, one inside, and one above. This small set of checks often reveals whether the problem is an endpoint choice, a reversed limit, an incorrect reference, or unexpected source data.
Frequently Asked Questions
What Excel formula checks if a number is between two numbers?
Use AND with one comparison for the lower limit and another for the upper limit. For inclusive endpoints, the value must be greater than or equal to the minimum and less than or equal to the maximum. The formula returns TRUE or FALSE.
Does Excel’s between formula include the two numbers?
It depends on the comparison operators. Using >= and <= includes values exactly equal to either limit. Using > and < excludes both endpoints. Choose the version that matches the rule you need to apply.
How do I check whether a value is between limits in reverse order?
Use MIN to identify the smaller limit and MAX to identify the larger one, then compare the value against those results. This allows the two input limits to be entered in either order without changing the interval being tested.
Can Excel highlight numbers between two values automatically?
Yes. Select the cells to format, create a formula-based rule under Home > Conditional Formatting > New Rule, and use an AND test that references the value in the selected range and the two limit cells. The formatting updates as the values change.
Why does my between formula return the wrong result?
Check the comparison operators, the order of the limits, and whether the cell references point to the intended values. Also verify that the inputs are numeric rather than text, that required cells are not blank, and that displayed rounding is not hiding additional decimal precision.
Related
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter: Show Unique Values and Remove DuplicatesIn an Excel table, open a column’s filter arrow to see its distinct values, then select the value…
- How to Extract Text in Excel With Formulas and ExamplesTo extract text in Excel, use functions such as LEFT, RIGHT, MID, and FIND to return the characters…
- Excel ABS Function: How to Return a Positive ValueTo turn a negative number into a positive one in Excel, use the ABS function: it returns the…
- Excel Date Difference Formulas for Days, Months, and YearsTo find the number of elapsed days between two dates in Excel, subtract the start date from the…
- Excel Page Break Formula: What Works and What Doesn’tNo Excel worksheet formula can insert a print page break or tell Excel exactly where a printed page…
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.