Featured image
IF in Excel
IF asks a question of a row and returns one of two answers: =IF(logical_test, value_if_true, [value_if_false]). The syntax takes about a minute to learn. What takes longer is the part that actually goes wrong, which is deciding where the boundary sits and, when IFs are nested, which test gets asked first.
=IF(logical_test, value_if_true, [value_if_false])
Almost every spreadsheet that does anything other than add up contains an IF. It is the point at which a sheet stops recording numbers and starts making a judgement about them: over target or under, eligible or not, pass or fail.
The function itself is three arguments and no surprises. The errors come from two places. The first is the boundary, whether somebody on exactly the threshold passes, which is the difference between > and >= and is invisible until the one row that sits on the line. The second is the order of nested tests, where a formula can be made of entirely correct comparisons and still answer wrongly because the broadest one was asked first.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- Anything that comes out TRUE or FALSE. Usually a comparison such as C2>10, but it can be a function that returns a boolean, such as ISBLANK or AND.
- What the cell shows when the test passes. Text goes in quotes, numbers do not, and this can itself be another formula, including another IF.
- optional
- What the cell shows when the test fails. Technically optional, and leaving it off gives you the word FALSE in the cell, which is almost never what anybody wants. Write something, even if it is "".
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
A plain pass or fail
=IF(C2>10, "Target met", "Below")Returns Target met
Ada sold 12 units, which is more than 10, so the first of the two answers comes back. Note the quotes: text in a formula needs them, and the commonest beginner error is a #NAME? caused by leaving them off.
Calculate in one branch and not the other
=IF(D5>=2000, D5*0.1, 0)Returns 0
The branches do not have to be text. Dara's revenue of 1200 fails the test, so the formula returns 0 rather than a commission. Writing 0 rather than leaving the argument off matters here: a cell holding the word FALSE cannot be added up.
Two conditions that must both hold
=IF(AND(C2>10, B2="North"), "Bonus", "No bonus")Returns Bonus
AND goes inside the test, not around the IF. It returns a single TRUE or FALSE for IF to act on. Swap it for OR and the meaning changes completely: OR passes a row that satisfies either condition, which on this table means four more rows qualify.
Nested IF for three outcomes
=IF(C4>=15, "High", IF(C4>=8, "Mid", "Low"))Returns High
The second IF sits in the first one's false branch, so it is only reached when the first test fails. Chen's 19 clears the first test and the inner IF is never evaluated.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Why nested IFs have to be in the right order
A nested IF stops at the first test that passes. That makes the order part of the logic rather than a matter of style, and it is the mistake that produces a formula where every individual comparison is correct and the answers are still wrong.
=IF(C4>=8, "Mid", IF(C4>=15, "High", "Low"))Returns Mid. The same two thresholds in the wrong order. Chen's 19 is caught by the >= 8 test before the >= 15 test is ever reached, so a high performer is labelled Mid and the High branch becomes unreachable for everybody.
Work from the narrowest band outwards: highest threshold first when the tests use >=, lowest first when they use <=. Then check the boundary row deliberately. Somebody with exactly 15 should land where the rules say they land, and the only way to know is to try it.
IF with AND, and IF with OR
- AND(test1, test2) is TRUE only when both hold. Use it for all of these must be true.
- OR(test1, test2) is TRUE when at least one holds. Use it for any of these is enough.
- NOT(test) flips a result, which is occasionally clearer than writing the opposite comparison.
The two are easy to mix up when the requirement is phrased in English, because people say and when they mean either. A rule like flag anyone in the North or South region is an OR; a rule like flag anyone in the North region and above target is an AND. When a flag column has far too many rows ticked, an OR where an AND was meant is the first thing to check.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=IF(C2>10, D2/0, 0)WhyThe error is not IF's. IF chose a branch, that branch contained a division by zero, and the error came back from it.
FixAn error inside an IF always comes from one of the branches. Evaluate each branch on its own in a spare cell to find which. If the division is legitimate but the denominator can be zero, IFERROR around the division, not around the whole IF, keeps the rest of the logic visible.
=IF(C2>10, Target, Below)WhyText in a formula without quotation marks. Excel reads Target as the name of something, finds no such name, and gives up.
FixPut quotes around any literal text: "Target met". Numbers never take quotes, and a number in quotes becomes text that will not add up later.
What goes wrong
These are the mistakes that come up again and again with IF. Each one is reasonable. That is why people make it.
Using > where >= was meant, or the other way round.
WhyIn conversation, over target and at or over target sound like the same sentence.
InsteadDecide explicitly what happens to somebody sitting exactly on the threshold, and then test that row. A boundary error affects one row in a hundred, which is exactly few enough to survive a review.
Nesting IFs from the broadest test to the narrowest.
WhyIt reads naturally left to right and each comparison on its own is right.
InsteadOrder the tests so the narrowest band is asked first. Then confirm every band actually appears somewhere in the output column.
Leaving the third argument off.
WhyIt is marked optional and the formula still evaluates.
InsteadSupply it. The default is the word FALSE in the cell, which is neither informative nor countable. Use "" if the answer really should be blank.
Writing =IF(C2>10, TRUE, FALSE).
WhyIt is an honest description of what the formula is doing.
InsteadC2>10 already returns TRUE or FALSE. The IF around it adds nothing but length.
Common questions
How do I write an IF THEN statement in Excel?
There is no THEN keyword. The commas do that work: =IF(test, what to show if true, what to show if false). For example =IF(C2>10, "Target met", "Below").
How many IFs can be nested?
Excel allows 64 levels, and anything past about three becomes unreadable and unmaintainable. Past that point use IFS, or a lookup table with VLOOKUP in approximate-match mode, which keeps the thresholds visible as data rather than buried in a formula.
What is the difference between IF with AND and IF with OR?
AND requires every condition to hold; OR requires at least one. A rule written in English with the word and in it is frequently an OR, so read the requirement again before choosing.
Why does my IF formula return FALSE?
Because the third argument was left off and the test failed. Excel's default for a missing value_if_false is the literal word FALSE. Supply the argument.
Is IF case sensitive?
No. =IF(B2="north", ...) matches North. Use EXACT inside the test if capitalisation has to matter.