Nested IFs: when to replace them with IFS, SWITCH or a table

The IF function is the first step towards spreadsheets that "decide": if the balance is negative write "Chase up", otherwise "OK". The trouble comes when the conditions become three, five, ten, and the formula turns into a chain of IFs one inside the other, with brackets nobody can count any more. There are more readable alternatives, and knowing how to choose between them makes sheets much easier to maintain.

The basics: IF, AND, OR

=IF(condition, value_if_true, value_if_false)

The condition is any expression that returns TRUE or FALSE: B2>1000, C2="Rome", D2<>"". To combine several conditions you use:

• AND, true only if all conditions are true: =IF(AND(B2>1000, C2="Rome"), "Priority", "");
• OR, true if at least one condition is true: =IF(OR(D2="Urgent", E2<TODAY()), "Check", "");
• NOT, which inverts the result.

So far everything is clear. The mess starts with multiple choices.

The problem with nested IFs

Suppose you have to assign a tier based on a customer's annual turnover: above 100,000 "Platinum", above 50,000 "Gold", above 20,000 "Silver", otherwise "Basic". With nested IFs:

=IF(B2>100000, "Platinum", IF(B2>50000, "Gold", IF(B2>20000, "Silver", "Basic")))

It works, but it has three flaws:

1. order matters: if you write the B2>20000 condition first, a 150,000 customer comes out as "Silver", because the first true condition wins;
2. the thresholds are inside the formula: to change them you have to edit every cell, or hope the formula was copied identically everywhere;
3. readability collapses beyond three levels.

First alternative: IFS

IFS (PIÙ.SE in Italian), available in recent versions of Excel, in Google Sheets and in LibreOffice Calc, lists pairs of condition and result:

=IFS(B2>100000, "Platinum", B2>50000, "Gold", B2>20000, "Silver", TRUE, "Basic")

The last pair, with TRUE as the condition, acts as the "otherwise": without it, a value that meets no condition produces #N/A. The formula is more linear, but flaws 1 and 2 remain: order still matters and the thresholds are still written into the formula.

Second alternative: SWITCH

SWITCH (same name in Italian Excel) suits cases where you compare a value against a list of exact values, not thresholds:

=SWITCH(C2, "N", "North", "C", "Centre", "S", "South", "Undefined")

The first argument is the value to examine, then come pairs of value and result; the last argument, if left unpaired, is the default result. It is more compact than IFS because it does not repeat C2= for every condition. It does not work, however, with comparisons such as "greater than".

The best solution: a lookup table

When the conditions represent business rules, such as bands, codes, rates or tiers, the right place for those rules is a table, not a formula. Create a small range, perhaps on a "Parameters" sheet:

Then use an approximate lookup:

=XLOOKUP(B2, Parameters!$A$2:$A$5, Parameters!$B$2:$B$5, , -1)

or, in versions without XLOOKUP, =VLOOKUP(B2, Parameters!$A$2:$B$5, 2, TRUE) with the first column sorted.

The advantages are concrete:

• the thresholds are visible and anyone can change them without touching the formulas;
• adding a tier means adding a row;
• the same table can feed other formulas, charts or drop-down lists.

For exact values, such as the region codes in the previous example, the same principle applies with an exact match.

How to choose

Writing tips

• Break lines in the formula bar with Alt+Enter (in Excel for Windows): a long formula split over several lines is much easier to read and the result does not change.
• Avoid returning numbers as text. "1" in quotes is text and will not be summed; write 1.
• Always provide for the remaining case. An IF without the third argument returns FALSE, which is misleading in a column of labels.
• Test the boundaries. With thresholds like "above 50,000", explicitly check what happens with exactly 50,000.

In my work, when a formula goes beyond three levels of IF, I take it as a signal: there is almost always a rule that deserves to become a table.

In short

• IF, AND and OR are enough for one or two conditions.
• In nested IFs and IFS the order of the conditions determines the result.
• SWITCH is the most compact choice for comparisons with exact values.
• Bands and rules that may change belong in a table, queried with a lookup.
• Always check boundary values and the remaining case.