Excel IF Between Two Numbers Function: What is it?
Mar 03, 2023
Last updated: August 23, 2026
Quick answer
Excel has no BETWEEN function. You build one by putting two comparisons inside AND, then wrapping that in IF: =IF(AND(A2>=10, A2<=20), "Yes", "No"). Use >= and <= to include the limits, or > and < to exclude them. Swap "Yes" for A2 to return the number itself when it falls in range.
Are you searching for a fast and straightforward approach to testing whether a value falls between two numbers in Excel?
You don't have to be an Excel expert or learn anything exotic. This guide shows you how to use Excel IF between two numbers, how to return a value instead of a label, how to handle several bands at once, and how to fix the two mistakes almost everyone makes on their first attempt.
Read Also: How to Combine Cells in Excel
Get our free Excel formulas cheat sheet
Plus new tutorials and template drops. Enter your email and we'll send it over.
You can also watch the video below to see how it's done if you are more of a visual learner. Like & subscribe for more of the best spreadsheet tips!
What this guide covers
- Does Excel have a BETWEEN function?
- How do you write an IF between two numbers formula?
- Should you include the limits or exclude them?
- How do you return a value from the range instead of Yes or No?
- What if the limits live in other columns?
- How do you handle several bands at once?
- How do you test whether a date is within the next or last N days?
- Formula reference table
- Why is your IF between formula not working?
- Frequently asked questions
Does Excel have a BETWEEN function?
No. There is no BETWEEN function in Excel, and typing =BETWEEN( returns #NAME?. Nor does SQL-style chaining work: =IF(10<A2<20, "Yes", "No") does not do what it looks like it does.
What you do instead is build the test out of two separate comparisons and join them with the AND function. AND returns TRUE only when every condition inside it is true, which is exactly the definition of "between".
The rule: "between" in Excel is always two comparisons and an AND. Everything else on this page is a variation on that sentence.
How do you write an IF between two numbers formula?
If you want to return a custom value when a number falls between two limits, put the AND formula in the logical test of the IF function.
Say the number you are testing sits in A2, the lower limit is 6 and the upper limit is 20. If the number is between 6 and 20 the answer should be "Yes", and if it is not, the answer should be "No".
IF between 6 and 20, limits typed straight into the formula:
=IF(AND(A2>6, A2<20), "Yes", "No")
Read the argument order carefully. IF takes three arguments in this order: the test, then the result when the test is TRUE, then the result when it is FALSE. So "Yes" has to come first here, because "Yes" is what you want when the number is in range. Putting them the other way round is the most common copy-paste mistake in Excel, and it fails silently: the formula returns a perfectly plausible-looking answer that happens to be exactly backwards.
IF between 6 and 20, limits held in cells B2 and C2:
=IF(AND(A2>B2, A2<C2), "Yes", "No")
Putting the threshold values in their own cells and referring to those cells is better practice than typing the numbers into the formula. When the limits change you edit two cells instead of every formula in the column.
Read Also: How to Unhide All Rows in Excel
Should you include the limits or exclude them?
This is the second thing people get wrong, and unlike the argument order it is a genuine judgement call rather than a mistake. It comes down to one character.
Exclusive, limits not counted. A value of exactly 6 or exactly 20 returns "No":
=IF(AND(A2>B2, A2<C2), "Yes", "No")
Inclusive, limits counted. A value of exactly 6 or exactly 20 returns "Yes":
=IF(AND(A2>=B2, A2<=C2), "Yes", "No")
The rule: add the equals sign when the boundary value belongs inside the band. In ordinary English "between 6 and 20" is ambiguous, and in Excel it is not, so decide deliberately. Grade boundaries, tax brackets and age bands are almost always inclusive. Physical tolerances are usually exclusive.
How do you return a value from the range instead of Yes or No?
You are not limited to text labels. Whatever you put in the second argument is what comes back when the test passes, and that can be the cell itself, another cell, a calculation, or an empty string.
If you have a set of values in column A and you want to know which ones fall between the numbers in column B and column C on the same row, use the formulas above. If you want the number itself returned when it qualifies, and a warning when it does not:
=IF(AND(J2>10, J2<20), J2, "Invalid")
To include the boundary values:
=IF(AND(J2>=10, J2<=20), J2, "Invalid")
To leave the cell looking empty instead of showing a warning, use two quotation marks with nothing between them:
=IF(AND(J2>=10, J2<=20), J2, "")
-
Prepare your data for your IF statements.
-
Select cell D2 and type the formula for your IF statement in the formula bar.
-
Press Enter, and you will get the result of the IF statement.
-
Use the Auto Fill handle in column D to copy the same formula down and get a result for every row.
The same four steps apply when you are returning the value itself rather than a label.
What if the limits live in other columns?
Everything above assumes you know which of the two limits is the smaller one. If your data is messier than that, and the lower and upper bounds could arrive in either order, wrap them in MIN and MAX so the formula sorts it out for you.
Use the MIN function to check that the target value is higher than the smaller of the two numbers, and the MAX function to check that it is lower than the larger of the two numbers.
To see if a number in A2 is between two other numbers in B2 and C2, in whichever order they happen to appear, use one of these:
Excluding the limits:
=AND(A2>MIN(B2, C2), A2<MAX(B2, C2))
Including the limits:
=AND(A2>=MIN(B2, C2), A2<=MAX(B2, C2))
Returning your own labels instead of TRUE or FALSE
Those two formulas return TRUE or FALSE. Wrap them in IF to return whatever you want:
=IF(AND(A2>MIN(B2, C2), A2<MAX(B2, C2)), "Yes", "No")
=IF(AND(A2>=MIN(B2, C2), A2<=MAX(B2, C2)), "Yes", "No")
-
To use this formula, prepare your data.
-
Select a cell and put the formula in the formula bar.
-
Select the data range in column D, right-click, and choose Fill Down.
-
After Fill Down, the IF result appears for every row.
Read Also: Everything You Need to Know About the Remainder Formula in Excel
How do you handle several bands at once?
One band needs one IF. Three or four bands, grade boundaries, shipping tiers, commission brackets, need a different shape, because stacking IF statements gets unreadable fast.
Nested IF, works in every version of Excel
Order matters here. Test the lowest band first and let each following test pick up whatever fell through:
=IF(A2<10, "Low", IF(A2<20, "Medium", IF(A2<30, "High", "Very high")))
Because each test only runs on values that failed the one before it, you do not need AND at all. That is the trick that makes tiered bands readable.
IFS, cleaner but version-gated
=IFS(A2<10, "Low", A2<20, "Medium", A2<30, "High", TRUE, "Very high")
The final TRUE acts as the catch-all, the equivalent of the last "value if false" in a nested IF. Without it, a value of 35 returns #N/A.
Version note: IFS arrived with Excel 2019 and Microsoft 365. In Excel 2016 or earlier it returns #NAME?, so use the nested IF version above.
A lookup table, best once you have more than four bands
Put your lower bounds in ascending order in one column and the labels beside them, then use VLOOKUP with its fourth argument left as TRUE:
=VLOOKUP(A2, $F$2:$G$5, 2, TRUE)
TRUE means approximate match, which finds the largest bound that is not greater than your value. The bounds column must be sorted ascending or the answers come back wrong with no error to warn you. The payoff is that changing a band later means editing the table, not rewriting a formula.
How do you test whether a date is within the next or last N days?
Because Excel stores dates as numbers, the same AND pattern works on dates with no changes. Use the TODAY function as one of the two limits and you get a test that updates itself every day.
Is the date within the next N days?
The first test checks that the target date is after today. The second checks that it is on or before today plus N days.
To test whether a date in A2 falls within the next nine days:
=IF(AND(A2>TODAY(), A2<=TODAY()+9), "Yes", "No")
-
Put the IF formula in the formula bar.
-
Select the column down to the last row of data, right-click, and choose Fill Down.
-
Every row now shows whether its date falls inside the nine-day window.
Is the date within the last N days?
Mirror the two tests. The first checks that the date is on or after today minus N days, the second that it is before today:
=IF(AND(A2>=TODAY()-9, A2<TODAY()), "Yes", "No")
One caution: TODAY() is volatile, so these formulas recalculate every time the workbook opens. That is the point when you want a live window, but it means a saved file will not preserve yesterday's answer. If a hidden timestamp is throwing the comparison off, strip the time from the date first, because TODAY() returns a whole day and a datetime never equals it.
Read Also: Learn How to Make a Graph in Excel With These Simple Steps
Formula reference table
| What you want | Formula | Notes |
|---|---|---|
| TRUE or FALSE, limits excluded | =AND(A2>10, A2<20) | No IF needed if TRUE and FALSE are fine |
| Yes or No, limits excluded | =IF(AND(A2>10, A2<20), "Yes", "No") | 10 and 20 themselves return No |
| Yes or No, limits included | =IF(AND(A2>=10, A2<=20), "Yes", "No") | 10 and 20 themselves return Yes |
| Limits stored in cells | =IF(AND(A2>=B2, A2<=C2), "Yes", "No") | Add $ signs if you will copy the formula across |
| Return the number itself when in range | =IF(AND(A2>=10, A2<=20), A2, "Invalid") | Second argument can be any value or formula |
| Leave the cell blank when out of range | =IF(AND(A2>=10, A2<=20), A2, "") | Looks empty but is a text string, not a true blank |
| Limits could be in either order | =IF(AND(A2>=MIN(B2,C2), A2<=MAX(B2,C2)), "Yes", "No") | MIN and MAX sort the bounds for you |
| Several bands, any Excel version | =IF(A2<10, "Low", IF(A2<20, "Medium", "High")) | Test lowest first, no AND required |
| Several bands, Excel 2019 and later | =IFS(A2<10, "Low", A2<20, "Medium", TRUE, "High") | The final TRUE is the catch-all |
| Many bands from a lookup table | =VLOOKUP(A2, $F$2:$G$5, 2, TRUE) | Bounds column must be sorted ascending |
| Date within the next 9 days | =IF(AND(A2>TODAY(), A2<=TODAY()+9), "Yes", "No") | Recalculates daily |
| Date within the last 9 days | =IF(AND(A2>=TODAY()-9, A2<TODAY()), "Yes", "No") | Recalculates daily |
Why is your IF between formula not working?
| What you see | What is actually happening | Fix |
|---|---|---|
| Every answer is exactly backwards | The TRUE and FALSE arguments are the wrong way round. This fails silently, no error at all | The second argument is what you want when the test passes. Put "Yes" first |
| #NAME? | You typed =BETWEEN(, or you used IFS on Excel 2016 or earlier | There is no BETWEEN function. Use IF with AND, or a nested IF instead of IFS |
| Every row returns "No", even the ones that are in range | You wrote =IF(10<A2<20, ...). Excel reads it as (10<A2)<20, and a TRUE or FALSE result never compares as less than a number, so the test is always FALSE | Split it into two comparisons inside AND |
| Boundary values give the wrong answer | You used > and < when you meant >= and <=, or the other way round | Add or remove the equals sign. Decide whether the limit belongs inside the band |
| You have too many arguments in this function | You left AND out and wrote =IF(A2>10, A2<20, "Yes", "No"), which hands IF four arguments when it only takes three | Wrap both comparisons in AND so the whole test counts as one argument: =IF(AND(A2>10, A2<20), "Yes", "No") |
| Numbers that look right return No | The values are text, not numbers. Text is left-aligned by default and never compares as a number | Select the column, run Data > Text to Columns, click Finish |
| The limits shift as you copy the formula down | B2 and C2 are relative references so they move with the formula | Lock them: $B$2 and $C$2 |
| A date test never matches | The cell holds a date plus a hidden time, so it is never equal to a whole-day value | Wrap it in INT, or remove the time from the date |
| #N/A from IFS or VLOOKUP | No band matched. IFS has no catch-all, or the VLOOKUP value is below the first bound | Add TRUE as the final IFS condition, or add a bottom row to the lookup table |
Final Thoughts on Excel IF Between Two Numbers
Excel gives you no BETWEEN function, but IF plus AND does the job in every version, and the pattern extends cleanly from one band to many. Two things are worth remembering after you close this page: the second argument of IF is the TRUE result, and the equals sign is what decides whether your limits count.
You can visit our homepage for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets Templates!
Frequently Asked Questions on Excel IF Between Two Numbers
Is there a BETWEEN function in Excel?
No. Excel has no BETWEEN function and typing =BETWEEN( returns #NAME?. Build the test from two comparisons joined by AND, then wrap it in IF: =IF(AND(A2>=10, A2<=20), "Yes", "No").
Can the IF statement have two conditions in Excel?
Yes. Use the AND function inside the logical test to require both conditions, or the OR function to require either one. AND is what makes a between test work, because a value is only in range when it clears the lower limit and the upper limit at the same time.
What are the three arguments of the IF function in Excel?
Microsoft names them logical_test, value_if_true and value_if_false, in that order. The second argument is what Excel returns when the test passes and the third is what it returns when the test fails. Swapping the last two is the most common cause of a between formula that answers backwards.
How do I write an IF formula for a value between two numbers and return a value?
Put the value you want in the second argument instead of a label. =IF(AND(A2>=10, A2<=20), A2, "Invalid") returns the number itself when it falls in range and the word Invalid when it does not. You can return another cell, a calculation, or "" for a blank-looking cell.
How do I check if a number is between two values in Excel without IF?
Use AND on its own: =AND(A2>=10, A2<=20). It returns TRUE or FALSE directly, which is enough for conditional formatting rules and for feeding into other formulas.
How do I handle more than one range, like grade bands?
Use a nested IF that tests the lowest band first, =IF(A2<10, "Low", IF(A2<20, "Medium", "High")), or IFS on Excel 2019 and later. Beyond four bands, a lookup table with VLOOKUP set to approximate match is easier to maintain.
Why does =IF(10<A2<20, "Yes", "No") always return No?
Excel does not support chained comparisons. It reads this left to right as (10<A2)<20, so the inner test returns the logical value TRUE or FALSE and Excel then compares that against 20. In Excel's comparison order every number ranks below any text, and text ranks below FALSE, which ranks below TRUE, so both TRUE<20 and FALSE<20 come out FALSE. The formula always takes the false branch and returns "No" for every value of A2, even ones that really are between 10 and 20, and no error appears to warn you. Split it into =IF(AND(A2>10, A2<20), "Yes", "No").
Related Articles:
The Top 5 Google Sheets Formulas You Need to Know
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
