Nested IF allows you to create IF statement with more than 2 results. In Excel, you can build nested IF with many functions. In this article, we will present many cases.
How to Create Nested IF Formulas in Excel
The IF function returns a maximum of 2 results according to the result of a logic test: TRUE or FALSE. However, in many situations, the conditions could exceed 2 results.
For instance, we have a database of customers, and some of them have premium statuses. We want to apply a specific percentage discount according to the status
- Gold (20%)
- Silver (10%)
- Bronze (5%)
With the IF function =IF(B2="Gold",20%,5%), we can only create a condition for one value, here 'Gold' 🫤 And, of course, the result is false when the value is 'Silver', 'Bronze', or empty. So, when you must analyse multi-criteria, you must write a nested IF formula in Excel.
How to write a nested IF with the IF function?
Originally, in Excel, this was the only way to manage multiple statements. The secret is to replace the FALSE argument with another IF function to test another situation. For instance, we can write another test in our previous function like this
- We have 2 IF functions, so we must close 2 parenthesis at the end of the function
- But it's still not what we want because we don't manage the case when the cell Bx is empty.
- We must write a 3rd IF function like this to cover all cases.
The formula works, and the result is correct, but such a formula is not easy to write because it's easy to make mistakes (like forgetting to close the proper number of parenthesis).
This is why, since Excel 2016, Microsoft has created the IFS function to simplify the writing of a nested IF.
How to use the IFS function?
The IFS function in Excel checks multiple conditions and returns a value for the first TRUE condition.
=IFS(condition1, result1, condition2, result2, …)
- Excel checks conditions in order and returns the first match.
- If no condition is TRUE, Excel gives an error.
- 💡 Tip: Add a final TRUE, "Default" to avoid errors 😉
In our example, we will write the following formula
Replace Nested IFs with VLOOKUP for Range-Based Lookups
Writing multiple nested IF functions can quickly become complex and hard to manage when dealing with grading systems, commission rates, or tax brackets. Imagine setting up a formula to assign a grade based on a score:
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
This approach works but becomes difficult to modify or expand. Instead, you can use VLOOKUP with the argument TRUE (or XLOOKUP), simplifying the process by searching for the closest match in a sorted table. This method works only for numerical values and efficiently handles range-based lookups.
👉 Return a Value Between Two Values in a Range.
