Excel IFS Between Two Numbers: Formula and Examples

Formulas & functions · Updated November 18, 2026

To check whether a value in A2 falls between 10 and 20, inclusive, use =IFS(AND(A2>=10,A2<=20),"In range",TRUE,"Out of range"). The AND function tests both limits, and IFS returns the result for the first condition that is true.

How IFS checks a range

Excel does not have a special IFS condition called “between.” Instead, you describe the lower and upper comparisons separately, then combine them with AND. For an inclusive range, the comparisons are greater than or equal to the lower limit and less than or equal to the upper limit. Both must be true for AND to return TRUE.

IFS evaluates logical tests from left to right. It returns the value paired with the first test that is true, then stops. In the opening formula, the first test checks whether A2 is from 10 through 20. The final pair, TRUE and “Out of range,” is a catch-all: TRUE is always true, so Excel uses that result if the range test fails.

The function’s general pattern is IFS(logical_test1,value_if_true1,logical_test2,value_if_true2,...). Every logical test needs a corresponding result. IFS is supported in Microsoft 365 and newer Excel releases; if an older version does not recognize it, Excel may show a #NAME? error. A nested IF formula is a practical fallback.

Choose inclusive or exclusive limits

Whether the endpoints count as part of the range depends on the question you are answering. The symbols >= and <= include values equal to a limit. Use > and < when the value must be strictly inside the limits.

Assume A2 contains the number to check and the interval must be strictly greater than 10 and strictly less than 20. Use =IFS(AND(A2>10,A2<20),"Inside",TRUE,"Outside or on a limit"). For example, 15 returns “Inside,” but 10 and 20 both return “Outside or on a limit.”

Choose the boundary rules before writing the formula, especially when multiple ranges meet. If one category ends at 10 and the next starts at 10, inclusive comparisons could put 10 in both categories if you test the ranges independently. In a single IFS formula, the first matching condition wins, so order can determine its category. A clear written rule—such as “10 belongs in the lower band”—helps prevent inconsistent results.

Classify a value into several bands

IFS is useful when a value needs one label from several ordered ranges. You can often write successive upper limits rather than repeat AND for every band. This works when the ranges are continuous and tested from the lowest threshold upward.

Assume A2 contains a score. The following formula labels negative scores, scores from 0 through 10, scores above 10 through 20, and scores above 20. With A2 equal to 15, it returns “Over 10 to 20.”

=IFS(A2<0,"Below zero",A2<=10,"0 to 10",A2<=20,"Over 10 to 20",TRUE,"Over 20")

Notice that the second test does not need to say A2>=0. If Excel has reached that test, the earlier condition A2<0 was false, so the value must be at least 0. Similarly, when Excel reaches A2<=20, the prior tests have already ruled out values at or below 10 and values below 0. This shortcut makes a sequence of ordered bands easier to read.

It is also possible to spell out every lower and upper limit using AND. That can be easier to audit when the ranges have gaps or special rules. For example, a category might apply to 10–20 and another to 30–40, with 21–29 deliberately left unclassified. In that case, explicit interval tests make the gap visible rather than silently assigning it to a neighboring band.

Build and enter the formula in Excel

On Excel for Microsoft 365 on Windows, select the result cell, type the formula, and press Enter. You can also type it in the formula bar. The formula itself works the same way in Excel for Mac and Excel for the web; the ribbon layout and some keyboard shortcuts differ, but there is no different IFS syntax for those platforms.

For a basic check, the assumed layout is a number in A2 and the answer in B2. Enter the formula below in B2; a value of 18 in A2 returns “In range.”

=IFS(AND(A2>=10,A2<=20),"In range",TRUE,"Out of range")

To apply the rule to more rows, copy B2 down using the fill handle, the small square at the lower-right corner of the selected cell. The relative reference A2 changes to A3, A4, and so on. If your limits are stored in cells—for example, the lower limit in D1 and upper limit in E1—you can lock those references with dollar signs before filling down. That keeps the thresholds fixed while the row being checked changes.

With the value in A2, the lower limit in D1, and the upper limit in E1, enter this in B2. If D1 is 10, E1 is 20, and A2 is 18, the result is “In range.”

=IFS(AND(A2>=$D$1,A2<=$E$1),"In range",TRUE,"Out of range")

For a small workbook, fixed limits written directly into a formula may be convenient. If the limits are likely to change, storing them in cells makes the rule easier to maintain: edit the threshold once instead of finding and replacing it in many formulas.

Handle blanks and unexpected entries

A blank input can be a source of misleading classifications. In numeric comparisons, Excel may treat a genuinely empty cell like zero in some contexts. If zero is inside the range, a blank might therefore appear to qualify even though no number was entered. Add a blank check before the range test when an empty input should produce an empty result.

Assume A2 is the input cell and the result formula goes in B2. This version leaves B2 visually blank when A2 is empty, returns “In range” for values from 10 through 20, and otherwise returns “Out of range.”

=IFS(A2="","",AND(A2>=10,A2<=20),"In range",TRUE,"Out of range")

If users might type words into the input cell, decide how those entries should be handled. A validation rule or a separate number check can make the workbook’s behavior clearer. For a simple sheet, you might use Data > Data Validation to restrict entries to whole numbers or decimals within a stated range. Data validation helps prevent mistakes during entry, but it does not replace formula logic: pasted data, imported values, or edits in other workflows may still need checking.

Also distinguish a number from text that looks like a number. A cell containing the numeric value 15 is not always equivalent to a text entry such as “15”; imported data can bring numbers in as text. If comparisons produce surprising results, check the cell’s value and data type, not just its appearance. Converting the source data to actual numbers can be more reliable than trying to disguise a data-quality issue inside a long IFS formula.

Order conditions and avoid gaps

Condition order is essential when ranges overlap. Since IFS stops at the first true test, an earlier broad condition can prevent a later, more specific condition from ever being used. Put the most specific rule first, or rewrite the conditions so that each interval has an unambiguous boundary.

For example, a sequence that first tests A2<=20 and later tests A2<=10 will assign a value of 8 to the first result. The second test is unreachable for values at or below 10. Reversing those tests may fix that particular issue, but the better habit is to lay out the intervals on paper and check every boundary value before filling the formula down.

Gaps matter too. Suppose the intended ranges are 0–10 and 20–30. A value of 15 belongs to neither range. If the formula ends with a broad TRUE fallback, it will receive whatever fallback label you choose; it will not automatically be flagged as an error. Use a meaningful catch-all such as “Check value” when an unmatched result might indicate a missing rule or incorrect input. Test the lower endpoint, upper endpoint, just-below and just-above values, and at least one value in any gap.

IFS compared with IF and lookup methods

IFS can be easier to read than several nested IF functions when each condition has a clear result. It is not automatically the best choice for every range rule. For just one interval, a simple IF with AND can be shorter and is compatible with older Excel versions that lack IFS.

Assume A2 contains the value to check and the result goes in B2. This equivalent IF formula returns “In range” for 10 through 20, inclusive, and “Out of range” otherwise.

=IF(AND(A2>=10,A2<=20),"In range","Out of range")

For a long list of changing thresholds, a small lookup table may be easier to update than a very long IFS formula. That approach is especially useful when someone needs to review or maintain the thresholds. The lookup method must be designed around the exact band rules, including whether each breakpoint belongs to the lower or upper band; approximate lookup behavior can be confusing if the table is unsorted or its threshold meanings are not clearly documented. For a few stable categories, IFS is often more direct.

Choose based on maintainability, not just the number of characters. IFS makes a sequence of tests explicit, IF is widely compatible and compact for a single decision, and a table can make frequently revised rules visible to non-formula users. Whatever method you use, include a final fallback and test the endpoints.

Common formula problems

If Excel displays an error or an unexpected answer, check the formula in a deliberate order:

  • #N/A: IFS found no true condition. Add a final TRUE condition and a suitable fallback result.
  • #NAME?: The Excel version may not support IFS, or the function name may be misspelled. Try a nested IF formula if the version is old.
  • A value is assigned to the wrong band: Review the order of the tests and whether the endpoints use >, >=, <, or <= as intended.
  • A blank appears to qualify: Add an explicit blank check before numeric comparisons, or verify that the input is a real number.
  • The formula is rejected: Check that every logical test has a matching result and that the parentheses and quotation marks are balanced. Regional settings can also affect which argument separator Excel expects when entering formulas.

When the formula is accepted but the result is wrong, temporarily test a few known values in separate rows. Include the lower and upper boundaries, values immediately on either side, a normal interior value, and an out-of-range value. This small test set often reveals a boundary or order error faster than inspecting a long column of outputs.

Frequently Asked Questions

Can I use IFS to check whether a number is between two values?
Yes. Put AND(lower_test,upper_test) in an IFS logical test, such as AND(A2>=10,A2<=20). Add a result for that condition and a final TRUE condition if you need a result when the value is outside the interval.

Does Excel include the two endpoints in a between check?
Only if you use inclusive operators: >= for the lower limit and <= for the upper limit. Use > and < to exclude the endpoints. Choose deliberately, because a value exactly on a boundary can otherwise be classified differently than intended.

Why does IFS return #N/A?
That usually means none of the logical tests evaluated to TRUE. Add a final TRUE condition with a fallback result, or revise the conditions to cover the values you expect. A fallback can also make gaps visible with a result such as “Check value.”

Can I use IFS with dates?
Yes. Excel stores valid dates as serial values, so comparisons with date limits can use the same AND pattern. Make sure the cells contain actual Excel dates, not text that merely looks like a date, and decide whether the start and end dates should be included.

What can I use if my Excel version does not support IFS?
Use nested IF functions or a single IF with AND for a simple interval. For one range, =IF(AND(A2>=10,A2<=20),"In range","Out of range") gives the same inclusive check as the opening example.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.