A standard IF function is great for handling two outcomes: if a condition is true, do one thing; if it's false, do another. But business rules are rarely that simple. What if you have three, four, or even ten possible outcomes? For years, the answer was to nest IF statements inside each other, creating a chain of logical tests.

Let's imagine a common scenario: calculating sales commissions. Your company has a tiered system. Depending on how much a salesperson sells, they fall into a specific performance category.

To assign these categories automatically in Excel, you could write a nested IF formula. If the sales amount in cell A2 is greater than 50,000, it returns "Gold". If not, it moves to the next IF statement to check if the amount is greater than 25,000. If that's also false, it defaults to the final value, "Bronze".

This works, but it can get messy. Each nested IF adds another layer of parentheses and complexity. Tracking the logic in a formula with five or six nests is a recipe for a headache. Thankfully, there are now cleaner ways to handle this.

Sometimes the question isn't about multiple outcomes, but about multiple conditions. You need to check if several things are true at the same time before making a decision. This is where the logical functions AND, OR, and NOT become essential partners for IF.

AND requires all conditions to be true. For example, to qualify for a bonus, an employee must exceed their sales target AND have a customer satisfaction score above 90%.

OR only requires at least one condition to be true. A transaction might be flagged for review if the amount is over $10,000 OR if the transaction is international.

NOT simply reverses the result of a logical test. It turns TRUE into FALSE and vice versa. You could use it to find all clients who are NOT in your active customer list.

Let's expand our commission example. To earn a special $500 bonus, a salesperson must achieve "Gold" status AND have worked at the company for more than two years. Assuming cell A2 has their sales and B2 has their years of service, the formula would look like this:

Here, the AND function first checks if both conditions are met. Only if both are true does it return TRUE to the IF function, which then assigns the $500 bonus. If either condition is false, AND returns FALSE, and the IF function assigns a 0.

As spreadsheets evolve, so do their functions. Excel introduced IFS and SWITCH to manage complex conditional logic more elegantly than nested IFs.

The IFS function lets you list pairs of conditions and values. It checks each condition in order and returns the value for the first one that evaluates to TRUE. It's a much more readable way to handle our tiered commission problem.

Notice there's no nesting. Each condition and its result are laid out sequentially. This makes the logic much easier to follow and edit.

The SWITCH function is different. It's designed to match an expression against a list of specific values. It works best when you have one cell whose value can be one of several distinct options, like a status code or a product name. For example, if A2 contained a rating from 1 to 3, SWITCH could translate it to text.

The final argument, "N/A", is the default value used if no matches are found. It's cleaner than an IF chain for exact-match scenarios.

Even the best formulas can break. Dividing by zero, looking for a value that doesn't exist, or referencing invalid cells can result in ugly error messages like #DIV/0!, #N/A, or #VALUE!. These errors not only look unprofessional, but they can also break downstream calculations that depend on them.

This is where error handling comes in. The IFERROR function is a simple but powerful wrapper you can put around any formula. It works like this: IFERROR(your_formula, value_if_error). If your_formula calculates successfully, IFERROR returns the result. If it produces any error, it returns value_if_error instead.

For instance, if you were calculating Sales / Customers in cell C2, but cell B2 (Customers) might sometimes be zero, you'd get a #DIV/0! error. Here's how to fix it:

Now, if B2 is 0, the formula will return 0 instead of an error, keeping your spreadsheet clean and functional.

A related function, IFNA, is more specific. It only catches the #N/A error, which typically occurs with lookup functions like VLOOKUP or XLOOKUP when they can't find a match. Using IFNA is good practice when you expect lookup failures but want to let other types of errors (like a typo in the formula) show up so you can fix them.

By moving beyond simple IFs and embracing these advanced functions, you can build spreadsheets that are not just powerful, but also logical, readable, and resilient.