IF is how a spreadsheet makes decisions. It tests a condition and returns one thing when that’s true, another when it’s false — “if the budget’s over, flag it; otherwise leave it.” From that one idea you can build almost any business rule: grading, categorizing, flagging, conditional math. This walks IF from its basic form through combining conditions with AND and OR, handling many outcomes with IFS, catching errors, and the mistakes that turn an IF formula into an unmaintainable tangle.
The basic IF
Every IF takes three parts: a test, what to return if it’s true, and what to return if it’s false.
=IF(B2>100, "Over budget", "OK")This checks whether B2 exceeds 100; if so it returns “Over budget,” otherwise “OK.” The test is any expression that resolves to TRUE or FALSE — a comparison (>, <, =, <> for “not equal”), a cell reference, or another function. The true and false results can be text (in quotes), numbers, or even other formulas. That’s the whole foundation, and most of what makes IF powerful is what you put in each of those three slots. Microsoft’s guide to IF with AND, OR, and NOT is the reference.
Combine conditions with AND and OR
A single test is often not enough — you need “both of these” or “either of these.” AND and OR wrap inside IF to combine conditions.
=IF(AND(B2>100, C2="Open"), "Follow up", "")
=IF(OR(B2>100, C2="VIP"), "Priority", "Normal")AND returns true only when every condition holds; OR returns true when any does. So the first formula flags a row only when it’s both over 100 and still open; the second marks it priority when it’s either a big amount or a VIP. You can stack several conditions inside each, and even nest AND inside OR for more complex logic. This is where IF starts to encode real rules — the kind of multi-part decision a person would otherwise make by eye, now made consistently by the formula every time. A quick way to keep AND versus OR straight: AND is strict (all conditions must pass, so it returns true less often), while OR is lenient (one is enough, so it returns true more often). If a flag is firing too rarely, you may have used AND where OR fits; if it’s firing on everything, the reverse. Reading the rule aloud — “flag it when it’s over budget and still open” — usually tells you which one you mean.
Many outcomes: nested IF vs IFS
When there are more than two possible results — letter grades, tiers, statuses — you need multiple branches. The old way nests IFs inside each other:
=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))Each false branch holds the next IF, cascading down until one matches. It works, but more than two or three levels become hard to read and easy to break. The modern fix is IFS, which lists conditions and results in sequence:
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")IFS reads each condition in order and returns the first that’s true, with a final TRUE acting as the catch-all “everything else.” It’s far more legible than deep nesting — no counting closing parentheses — and it’s the better choice for any multi-way decision on Excel 365 or 2021. For older versions, nested IF still rules, so it’s worth knowing both. And there’s a third option that beats both once the branches really multiply: a lookup table. Instead of a formula with eight conditions, list the thresholds and results in a small range and use an approximate-match VLOOKUP against it — easier to read, easier to change, and no formula surgery when a threshold shifts.
The operators (and NOT)
IF’s test is built from comparison operators, and it’s worth having them straight — a wrong operator is a common source of logic that looks right but isn’t.
| Operator | Means |
|---|---|
= |
equals |
<> |
does not equal |
> >= |
greater than / or equal to |
< <= |
less than / or equal to |
The one people forget is <> for “not equal” — =IF(C2<>"Done", "Pending", "") flags everything that isn’t finished. There’s also a NOT function that inverts a condition: NOT(C2="Done") is the same as C2<>"Done", useful when you want to reverse a more complex test. And remember text comparisons aren’t case-sensitive by default — “DONE” and “done” both match ="Done" — which is usually what you want, but worth knowing when case actually matters.
Catch errors with IFERROR
IF’s close cousin handles the case where a formula might fail. IFERROR returns a fallback instead of an ugly error code.
=IFERROR(A2/B2, "Check data")If B2 is empty and the division fails, IFERROR catches the #DIV/0! error and returns “Check data” instead. It’s the clean way to keep a report free of error codes — wrap a lookup, a division, or any formula that can fail. Use it deliberately, though: IFERROR hides every error, so add it only once a formula is proven correct, or it’ll mask a real mistake as cheerfully as an expected one.
IF for flags and conditional math
Beyond returning labels, IF builds the TRUE/FALSE flags and conditional values that drive the rest of a spreadsheet.
- ✓ Return 1 or 0 → SUM the column to count how many rows match a condition
- ✓ Return a value or blank → feed clean numbers into a total, hiding the rest
- ✓ Return a category label → create a field you can group on in a pivot
- ✓ Return TRUE/FALSE → drive conditional formatting and validation logic
An IF that returns 1 or 0 creates a flag column you can SUM to count matches, or use as a helper for charts and pivots. An IF returning a number or blank — =IF(C2="Paid", B2, "") — feeds conditional totals and keeps unwanted values out of a calculation. IF also pairs with the logic in conditional formatting and data validation, where the same TRUE/FALSE thinking decides what gets colored or accepted. Once you see IF as “produce a value based on a condition,” it becomes a building block you reach for constantly, not just a labeling tool.
The mistakes that tangle IF formulas
"A", not A) or you’ll get a #NAME? error. And mind the order in nested IF/IFS: conditions are checked top-down, so >=70 placed before >=90 would catch every score and the higher grades would never fire.
That ordering trap is the subtle one — because IFS stops at the first true condition, a too-broad condition placed early swallows everything below it, and the formula looks right while quietly returning wrong results. Build multi-branch logic from the most specific or highest threshold down, test it on a few known values, and switch to a lookup table once the branches multiply. Master the basic IF, layer AND/OR for combined conditions, reach for IFS over deep nesting, and wrap fragile parts in IFERROR — with those four moves, IF handles virtually any decision you need a spreadsheet to make, and the essential formulas guide shows where it fits among the rest.