Excel's Logical Functions: IF, IFS, AND/OR and the Ladder That Breaks at Three Rungs

Most formulas in a workbook calculate something. Logical formulas decide something — whether an order qualifies for a discount, whether a shipment counts as late, whether a row belongs in this report at all. That difference matters more than it sounds, because a calculation that goes wrong is usually obvious and a decision that goes wrong usually is not. A total that reads 98,000 when the orders add up to 103,830 gets queried by lunchtime; a discount column that pays 5% where it should pay 12% looks exactly like a discount column that works.

This guide is about writing decisions you can still read in March. It covers IF and the argument everybody drops, nested ladders and the ordering bug that lives inside them, IFS and the default it does not give you, AND/OR/NOT and their surprising behaviour on ranges, the boolean arithmetic that replaces them inside array formulas, IFERROR versus IFNA, SWITCH, and the point at which the right answer is to stop nesting and build a table.

Tip: Every example below runs on the order table shown after section 1. Copy it into a blank sheet starting at A1 and the cell references line up exactly. IFS and SWITCH need Excel 2019 or later; XLOOKUP in section 9 needs Excel 365 or 2021. Everything else works in any version still in use.

1) What IF Actually Returns

=IF(logical_test, value_if_true, [value_if_false])

The mental model that causes the least trouble: IF does not "run" one branch or the other. It evaluates the test to TRUE or FALSE, then hands back one of two values it was already holding. It is a chooser, not a controller.

=IF(F2>7, "Late", "On time")

Result: On time — SO-1041 shipped in 2 days.

The third argument is optional, and that option is a trap. Leave it out:

=IF(F2>7, "Late")

and a fast order does not return a blank. It returns the literal word FALSE, in the middle of your report, in a column of text. Excel had to return something, and FALSE is what it has. If you want nothing, say nothing explicitly:

=IF(F2>7, "Late", "")

Those two quotes are an empty text string, not an empty cell, which is worth knowing before you point COUNTBLANK or ISBLANK at the result and get an answer you did not expect. A cell holding "" is not blank; it holds a string of length zero.

2) The Test: Comparison Operators and Two Quiet Traps

🎯 Scenario: You want to flag the orders large enough to need a second signature, before anyone argues about what "large" means.

Six operators do nearly all the work: =, <>, >, <, >=, <=. Any of them produces TRUE or FALSE on its own, with no IF around it at all — type =E2>=10000 in an empty cell and you get TRUE. That is the single most useful debugging habit in this whole article: when a decision comes out wrong, pull the test out of the IF and look at what it actually returns.

Trap one: text comparison ignores case. =C2="gold" returns TRUE for a cell containing Gold. That is convenient right up until it is not — if your data genuinely distinguishes ABC from abc (product codes sometimes do), = will not see the difference and you need =EXACT(C2,"Gold") instead.

Trap two: numbers that are text. A value imported as text sits left-aligned in the cell and fails every numeric comparison silently. ="12400">=10000 is TRUE, but for the wrong reason — Excel compares a text string against a number and text always sorts above numbers, so every text value passes every >= test against a number. A discount column built on that is 100% generous. =ISNUMBER(E2) down the column takes ten seconds and settles it.

Pitfall: =IF(E2>=10000, "Yes", "No") and =IF(E2>10000, "Yes", "No") differ on exactly one value: 10,000 itself. Nobody notices until the one order that lands precisely on the boundary shows up, and by then the rule has been in production for two quarters. Decide out loud whether the boundary is in or out, then write the operator that says so.

3) Nested IF: Banding, and the Order That Decides the Answer

🎯 Scenario: Volume discount — 12% at 20,000, 8% at 10,000, 5% at 5,000, nothing below that.

An IF inside the value_if_false slot of another IF is how one test becomes a ladder:

=IF(E2>=20000, 12%, IF(E2>=10000, 8%, IF(E2>=5000, 5%, 0)))

Result down the column: 8%, 0%, 12%, 0%, 5%, 5%, 0%, 8%, 0%, 12%.

Read it as a sequence of questions asked in order, where the first TRUE wins and everything below it is never evaluated. 12,400 fails the 20,000 test, passes the 10,000 test, and stops there.

That "first TRUE wins" rule is the whole game, and it is where the classic bug lives. Write the same bands ascending:

=IF(E2>=5000, 5%, IF(E2>=10000, 8%, IF(E2>=20000, 12%, 0)))

Result for SO-1043: 5% on an order of 28,700 — because 28,700 is indeed at least 5,000, and the first rung caught it. The formula has no error, no warning, no colour. It returns a plausible number and underpays your biggest customer by nearly 2,000. Overlapping conditions must be tested from the most specific end inward: descending bands descending, ascending bands ascending.

The other thing worth doing here is showing the ladder the value it will actually meet. Test it against 4,900 (SO-1049) and 5,000, then against 19,999 and 20,000. Four checks, and every boundary in the rule is pinned down.

Pitfall: Excel allows 64 levels of nesting, which is not permission. Past three rungs the formula stops being readable, the closing parentheses stop being countable, and — the real cost — the rule stops being visible to anyone who is not editing the formula bar. Section 9 is about what to do instead.

4) IFS: The Same Ladder Without the Parenthesis Pile

=IFS(test1, value1, [test2, value2], ...)

IFS takes the ladder and flattens it into pairs, which is the same logic with a quarter of the punctuation:

=IFS(E2>=20000, 12%, E2>=10000, 8%, E2>=5000, 5%, TRUE, 0)

Result: 8% for row 2 — identical to the nested version, and now the four bands read down the formula like the rows of a table.

The pairs are still evaluated top to bottom and the first TRUE still wins, so section 3's ordering bug transfers here intact. What does not transfer is the else. A nested ladder's final value_if_false is the catch-all; IFS has no such slot, and an IFS where nothing matches returns #N/A:

=IFS(E3>=20000, 12%, E3>=10000, 8%, E3>=5000, 5%)

Result for SO-1042 (3,150): #N/A.

The idiom for a default is a final pair whose test is the literal TRUE — it always matches, so it catches everything that fell through. Some people prefer 1=1; both work and TRUE is clearer.

Tip: IFS arrived in Excel 2019. In Excel 2016 and earlier it is #NAME?, and the file will still open — it just shows an error where a number used to be. If the workbook travels, nested IF remains the compatible choice.

5) AND, OR, NOT — and Why They Collapse a Column

🎯 Scenario: Every order needs review if it took more than 10 days to ship or it is worth 25,000 or more.

=AND(test1, test2, ...)     all must be TRUE
=OR(test1, test2, ...)      at least one must be TRUE
=NOT(test)                  flips TRUE and FALSE

They are almost always found inside IF's first argument:

=IF(OR(F2>10, E2>=25000), "Review", "OK")

Result down the column: four Review rows — SO-1043 (28,700), SO-1045 (12 days), SO-1049 (11 days) and SO-1050 (14 days). The other six read OK.

AND narrows instead of widens:

=IF(AND(C2="Gold", E2>=20000), "Key account", "")

Result: Key account on two rows only — SO-1043 and SO-1050. SO-1041 is Gold but only 12,400; SO-1048 clears 15,250 but is Silver.

NOT is mostly for readability. =NOT(G2=0) and =G2<>0 return the same thing; use whichever one reads like the sentence you would say out loud.

Now the behaviour that catches everybody. AND and OR do not work row by row across a range — they consume everything you give them and return one value:

=AND(F2:F11>10)

Result: a single FALSE — meaning "not every order took more than 10 days", which is true and useless. There is no way to get a ten-row answer out of it, because AND is an aggregator by design. The moment you want a per-row TRUE/FALSE from combined conditions inside one formula, you need section 6.

6) Boolean Arithmetic: * for AND, + for OR

Excel stores TRUE as 1 and FALSE as 0 the instant you do arithmetic on it. That one fact replaces AND and OR everywhere they cannot go.

🎯 Scenario: Count the Gold orders worth at least 20,000, without adding a helper column.

=SUMPRODUCT((C2:C11="Gold")*(E2:E11>=20000))

Result: 2

Each bracket produces a ten-value list of TRUE/FALSE; multiplying them pairs up the rows and gives 1 only where both held; SUMPRODUCT adds the 1s. Swap * for + and you get the OR count — with one catch, because a row satisfying both conditions would contribute 2:

=SUMPRODUCT(--((C2:C11="Gold")+(F2:F11>10)>0))

Result: 5 — the four Gold orders plus SO-1049, a Silver order that took 11 days. Comparing the sum to >0 turns "how many conditions matched" back into a plain yes/no before counting.

That leading -- is the double unary minus, and it exists because SUMPRODUCT adds numbers, not booleans. Negate once to get −1/0, negate again to get 1/0. Multiplying by 1 does the same job if you find *1 easier to read:

=SUMPRODUCT(--(E2:E11>=10000))

Result: 4 — orders SO-1041, SO-1043, SO-1048 and SO-1050.

Tip: For plain counting, COUNTIFS is shorter and faster, and you should use it. Boolean arithmetic earns its place when the condition is something COUNTIFS cannot express — a comparison between two columns, a calculation inside the test, an OR across different fields — or when you need the per-row TRUE/FALSE list itself to feed FILTER.

7) IFERROR and IFNA: Catch the Right Error Only

🎯 Scenario: Average unit price per order. SO-1047 was cancelled and sits in the ledger with zero units.

=E2/D2

Result: 25.83 on row 2, and #DIV/0! on row 8, which then poisons every total that includes it.

=IFERROR(E2/D2, "—")

Result: 25.83, and an em dash on the cancelled order instead of an error.

IFERROR catches all of them: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!. That is its convenience and its danger. A lookup wrapped in IFERROR(..., 0) returns 0 when the value genuinely is not there — correct — and also returns 0 when you mistyped the function name, pointed at a deleted range, or handed it text where it wanted a number. The workbook shows a clean column of zeros and no sign that anything is wrong. This is the single most effective way to hide a broken formula from yourself for six months.

IFNA is the disciplined version — it catches #N/A and nothing else:

=IFNA(XLOOKUP(A2, $J$2:$J$20, $K$2:$K$20), "Not on price list")

A missing product is handled; a #REF! from a deleted column still shows up as #REF!, which is exactly what you want, because that one is your bug and not your data's.

Pitfall: wrap the narrowest thing that can fail, not the whole formula. =IFERROR(A*B/C + VLOOKUP(...), 0) hides failures from four different places behind one zero. =A*B/IFERROR(C,1) + IFNA(VLOOKUP(...),0) says what each fallback is for.

8) SWITCH: For Matching a Value, Not Testing a Condition

=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

When every rung of your ladder compares the same cell to a different fixed value, SWITCH says it in half the space:

=SWITCH(C2, "Gold", 12%, "Silver", 6%, "Bronze", 2%, 0)

Result: 12% for row 2. The lone trailing argument is the default, used when nothing matched — a tier of Platinum returns 0 rather than #N/A.

The limit is in the name: SWITCH matches values, so it cannot express >=20000. There is a well-known workaround — =SWITCH(TRUE, E2>=20000, 12%, E2>=10000, 8%, TRUE, 0) — which works because each test evaluates to TRUE or FALSE and the first one matching TRUE wins. It is clever, and if you have IFS you should use IFS instead; it expresses the same thing without the indirection.

9) When Logic Should Stop Being a Formula

Sections 3 and 4 both hard-code four discount bands into every cell of a column. That works and it is what most workbooks do, and it has three costs that only show up later: nobody can see the rule without clicking into the formula bar, changing a band means editing every formula that carries it, and there is no record anywhere of what the bands were last quarter.

🎯 Scenario: Same discount ladder, but finance wants to change the 10,000 band to 12,000 next month without touching a single formula.

Put the rule in cells. In J1:K5:

=XLOOKUP(E2, $J$2:$J$5, $K$2:$K$5, 0, -1)

Result: 8% for 12,400 — identical to the ladder. The -1 is the match mode: exact match, or the next smaller item, which is precisely what a band is. The older equivalent is =VLOOKUP(E2, $J$2:$K$5, 2, TRUE), whose TRUE means the same thing and which requires the band column sorted ascending.

Now the rule is four rows anyone can read and edit, the formula never changes, and last quarter's bands are one cell comment away from being documented. The same move applies to tier rates, shipping zones, approval thresholds, grade boundaries — any decision that is really a small table wearing a formula's clothes.

Two related habits worth stealing:

Name the flag. =IF(OR(F2>10, E2>=25000), "Review", "OK") in a column headed Review Reason is more useful as two columns — one that decides, one that says why — than as a single mega-formula nested four deep. Helper columns cost nothing and can be hidden.

Return values, not sentences. IF returning 8% can be summed, charted and compared. IF returning "8% discount" can only be read. Decide in numbers and format them as text at the very end, if at all.

10) Ten Ways Logic Goes Wrong

1. Omitting value_if_false — the word FALSE lands in a text column.
2. Bands tested in the wrong order — the widest condition first catches everything below it, silently.
3. IFS with no TRUE catch-all — #N/A for every row that matches nothing.
4. > where you meant >= — one row, exactly on the boundary, wrong forever.
5. Numbers stored as text — every comparison against a number passes, so every row qualifies.
6. AND fed a range — one FALSE for the whole column, and it looks like an answer.
7. IFERROR wrapping the entire formula — hides your typo as neatly as it hides missing data.
8. "" mistaken for blank — ISBLANK says FALSE, COUNTBLANK disagrees with COUNTA, and nobody can see why.
9. Comparing to a hard-coded date or rate — =IF(E2>=10000,...) repeated 400 times is 400 places to edit.
10. Nesting past three levels — technically legal, practically a rule nobody will ever audit again.

Conclusion

The functions here are not hard. IF has three arguments and AND has one idea. What makes logical formulas the ones most likely to be quietly wrong is that a bad decision returns a perfectly ordinary-looking value — no error, no colour, no complaint — and then keeps returning it every month until someone reconciles a number by hand and finds the gap.

So the habits are worth more than the syntax. Pull the test out of the IF and look at what it returns before you trust it. Check your bands against the values sitting exactly on the boundary, not the ones in the middle. Give IFS its TRUE rung and IFERROR the narrowest possible thing to catch. And when a ladder reaches three rungs, take the rule out of the formula and put it in cells where a human being can read it.

Want to practise? The conditional logic exercises in the app are built on exactly these shapes — a banding ladder with a boundary value in the data, an OR flag across two columns, and one where IFERROR is hiding something it should not be.