IF is Excel’s decision function. =IF(logical_test, value_if_true, value_if_false) checks whether something is true, then returns one value if it is and a different value if it is not. The test is a comparison, C2>B2 say; when it holds, you get the second argument, and otherwise the third.
One IF makes one decision. Real workbooks stack several: discount tiers, grade bands, status flags. That stacking is where the mistakes creep in, the loud ones that error and the quiet ones that don’t.
How do I write an IF formula in Excel?
The first two arguments are required, the third is optional (though you should supply it, more on that below). Here a status column flags which budget lines ran over:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Budget | Actual | Status |
| 2 | Travel | 500 | 620 | Over budget |
| 3 | Software | 300 | 280 | OK |
| 4 | Training | 400 | 400 | OK |
Fill the formula down and each row makes its own comparison. Notice row 4: actual spend of 400 against a budget of 400 reads OK, because 400>400 is false. That is correct here, but keep it in mind. Whether a boundary value lands in the true branch or the false branch is decided by that one > versus >= character, and it is where IF formulas go wrong most quietly.
The branches don’t have to be text. They can be numbers, cell references, or calculations. Using the same table, this returns the overspend amount instead of a label:
For the Travel row that is 620-500, so 120. For the rows at or under budget it returns 0.
How do multiple IF statements work in one formula?
To test more than one condition, you put a second IF where the value_if_false goes. Excel works through the conditions in order: it checks the first, and only when that fails does it move to the next. The first condition that passes wins, and the later branches no longer matter for the result. Nesting chains conditions in sequence like this; when you instead need several conditions to hold in a single test, all of them or any of them, wrap them in the AND, OR, and NOT functions rather than stacking more IFs.
Here is a realistic tiered discount: orders of 1,000 or more get 10%, orders of 500 or more get 5%, everything else gets nothing.
| A | B | |
|---|---|---|
| 1 | Order total | Discount |
| 2 | 1250 | 10% |
| 3 | 800 | 5% |
| 4 | 320 | 0% |
Because the first passing condition wins, the order of the conditions is part of the logic. This formula tests the biggest threshold first, which is why it works. Swap the tiers around and it stops working, without a single error:
Microsoft’s own guidance on nesting is blunt: “While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so.” And on why: “Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end.”
Their worked example is a grading formula (the D2 here is the score column in Microsoft’s example sheet, not the discount table above):
Four levels deep and it is already unpleasant: four conditions that must stay in descending order, four closing parentheses to balance, and a boundary at every step. Each tier you add makes the formula harder to read and gives a future editor one more place to break it. Past two or three levels, stop nesting. Use IFS, or replace the whole ladder with a lookup against a small tier table, which is what Microsoft’s pitfalls page also suggests; the XLOOKUP guide covers that pattern.
What is the IFS function and when should I use it?
IFS checks conditions in order and returns the value paired with the first one that is TRUE. Same logic as a nested IF, written flat:
The discount ladder becomes:
Each condition sits next to its result, there is one closing parenthesis, and the order still matters exactly as before: biggest threshold first. Microsoft documents support for “up to 127 different conditions”, which is 127 more than any formula a colleague should have to read, but the headroom is there.
Two behaviors are worth knowing before you rely on it:
#N/Awhen nothing is TRUE. IF always has an else branch; IFS does not. Per Microsoft, “If no TRUE conditions are found, this function returns #N/A error.” TheTRUE, 0%pair at the end of the formula above is the documented fix: “To specify a default result, enter TRUE for your final logical_test argument.” TRUE is always true, so it catches everything the earlier conditions missed.#NAME?in versions without IFS. Microsoft’s IFS page lists Excel 2019, Excel 2021, Excel 2024, and Excel for Microsoft 365; Excel 2016 is not on the list. Open an IFS workbook in a version without the function and the formula shows#NAME?instead of a result.
Why is my IF formula not working?
The failures split into errors you can see and results that are merely wrong. The visible ones first:
#NAME? means Excel doesn’t recognize something in the formula. The usual cause is a misspelled function name; Microsoft’s troubleshooting page adds two more, text values missing their quotation marks and a range reference missing its colon. For IFS there is an extra one: the workbook was opened in an Excel version that doesn’t have the function, so the function name itself is unknown there and every cell using it errors.
#VALUE! has two usual causes with IF: the syntax is malformed, easy to do four parentheses deep, or the formula references a cell that already contains an error. One sneaky variant is typing a threshold with a thousands separator, =IF(A2>1,000, ...), which splits the number at the comma. Microsoft’s warning here is hard to improve on: “The evaluation values in formulas don’t have commas. If you add them, the IF function will try to use them as arguments and Excel will yell at you.” IFS adds one more case: a condition that doesn’t resolve to TRUE or FALSE at all.
A 0 you didn’t ask for usually means an empty branch. Write =IF(A2>500, "Discount",) with that trailing comma and the false branch exists but holds nothing, so Excel shows 0. Microsoft lists exactly this among IF’s common problems. Give both branches an explicit value, even if one is just "".
If you’re staring at an error code this page hasn’t covered, Excel errors explained walks through every one of them and how to trace it back.
Can I combine IF with VLOOKUP or XLOOKUP?
Yes, and it’s a common pattern: look a value up, then decide something about it. Say A2:B6 holds product codes in column A and stock counts in column B, with a code to check in D2:
The VLOOKUP fetches the stock count, the IF turns it into an instruction. The catch is what happens when the lookup misses: instead of one of your branches, the cell shows the lookup’s own error. The causes and fixes, including the IFNA versus IFERROR trade-off for handling an expected miss, are covered in why your VLOOKUP returns #N/A. If you’re on a current Excel, XLOOKUP sidesteps the wrapper entirely with its built-in not-found argument.
One boundary worth drawing: IF labels each row, one at a time. If what you actually want is to count or total the rows that meet a condition, that is a different family of functions, and COUNTIF and SUMIF do it in a single formula.
The IF mistake that doesn’t show up as an error
Every error above announces itself. The dangerous version of a broken IF doesn’t.
Take the discount ladder again, written with > instead of >=:
Nothing about that formula looks wrong. It calculates on every row, it returns a plausible percentage, and it is off by half right at the boundary. The same failure hides in a reordered condition, a tier threshold updated in one copy of the formula but not another, or a band edge that two people remembered differently. A wrong boundary returns a confident number, the number lands in a total, and the total ships.
So treat condition logic as something to check at its edges: test the exact threshold values themselves, and one value either side. And when a tier structure changes in a workbook other people rely on, compare the file before and after the edit. Seeing every changed formula and every changed result in one list is a much better safety net than hoping someone re-reads the parentheses.
