Complex Logical Functions

Beyond Simple Choices

You already know how the basic IF function works: it checks a condition and returns one value if it's true, and another if it's false. But what happens when you need to handle more than two outcomes? For example, assigning a letter grade based on a score, or categorising sales into multiple tiers.

This is where nested IF statements come in. The idea is simple: you place an IF function inside another IF function, usually in the value_if_false argument. This creates a chain of decisions.

Imagine you want to grade scores. If a score in cell A2 is over 90, it's an 'A'. If not, you need to check if it's over 80 for a 'B', and so on. Each new check becomes another IF function.

\text{=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))}

Nested IFs are powerful, but they have a downside. As you add more conditions, the formula becomes long and difficult to read. is a common frustration, as keeping track of all the closing brackets can be a real headache. Mismatched parentheses are a frequent source of errors in complex formulas.

A Cleaner Way with IFS

To solve the readability problem of nested IFs, Microsoft introduced the IFS function. It's designed to handle multiple conditions in a more direct, linear way. Instead of nesting, you just list your conditions and their corresponding outcomes in pairs.

The IFS function stops as soon as it finds the first true condition, so the order of your checks still matters. You should always list the most restrictive conditions first.

=IFS(
  A2>=90, "A",  // Test 1, Result 1
  A2>=80, "B",  // Test 2, Result 2
  A2>=70, "C",  // Test 3, Result 3
  A2<70,  "D"   // Test 4, Result 4
)

Notice how much cleaner this is. Each condition and its result are on the same level, making the logic much easier to follow. There's no complex nesting, just a straightforward list of tests. For a catch-all case, you can end with a condition that is always true, like TRUE or 1=1, to specify a default value if none of the other conditions are met.

Exact Matches with SWITCH

Sometimes you don't need to check a range or a complex condition. Instead, you need to check a single cell against a list of specific, exact values. For this job, the SWITCH function is more efficient than IF or IFS.

SWITCH takes an expression (like a cell reference), compares it to a list of values, and returns the result corresponding to the first match it finds. It also lets you specify a default value if no matches are found.

For example, let's say cell B2 contains a single letter code ('S', 'M', 'L') for T-shirt sizes, and you want to convert it to the full name ('Small', 'Medium', 'Large').

=SWITCH(
  B2,         // The expression to evaluate
  "S", "Small",  // Value 1, Result 1
  "M", "Medium", // Value 2, Result 2
  "L", "Large",  // Value 3, Result 3
  "Unknown"   // Default value if no match
)

This is far more concise than writing IF(B2="S", "Small", IF(B2="M", ...)). SWITCH is purpose-built for these one-to-one mapping scenarios and is the best tool for the job.

Layering Logic with AND, OR, NOT

What if your decision depends on more than one condition being true at the same time? Or if it depends on at least one of several conditions being true? This is where you combine logical functions like IF with the Boolean operators AND, OR, and NOT.

AND checks if all its arguments are true.
OR checks if at least one of its arguments is true.
NOT reverses the logical value of its argument (true becomes false, false becomes true).

You embed these functions inside the logical_test part of an IF or IFS function.

Imagine a sales bonus scenario. To get a bonus, a salesperson (in row 2) must have sales (C2) over $50,000 and a customer satisfaction score (D2) greater than 90%.

=IF(AND(C2 > 50000, D2 > 0.9), "Bonus", "No Bonus")

Now, let's change the rule. A bonus is awarded if sales are over $50,000 or if they are a top performer in a special category (E2 equals "Yes").

=IF(OR(C2 > 50000, E2 = "Yes"), "Bonus", "No Bonus")

By combining these operators, you can build very that reflect complex business rules. A good rule of thumb is to keep your nesting to a reasonable depth. If a formula has more than three or four nested levels, it's often a sign that you should rethink your approach, perhaps by using the IFS function, breaking the problem into smaller helper columns, or using a different tool like VLOOKUP or SUMIFS.

Let's review these powerful functions.

Quiz Questions 1/5

Which function is best suited for checking a single cell against a list of specific, exact values, such as converting a product code like "S" to "Small", "M" to "Medium", and "L" to "Large"?

Quiz Questions 2/5

When using the IFS function to evaluate a series of conditions, the order of the conditions does not matter.

Mastering Dynamic Excel Formulas