How to Use Multiple IF Functions? (Handling Multiple Conditions)
This article explains how to use the IF function multiple times in Excel to evaluate several conditions and return results based on them (known as multiple branching). This allows you to perform complex evaluations within a single cell.
Using Multiple IF Functions
In Excel, you can use nested IF functions to evaluate multiple conditions. This method, called “multiple branching,” lets you return different results based on each condition.
The basic syntax is as follows:
=IF(condition1, result1, IF(condition2, result2, result3))
In this formula, condition1 is evaluated first. If it’s true, result1 is returned. If condition1 is false, condition2 is evaluated. If condition2 is true, result2 is returned. Otherwise, result3 is returned.
Example: Grading Students with IF Functions
The table below assigns grades to students based on their scores. If a student scores 90 or higher, they receive an “A”; scores between 70 and 89 receive a “B”; scores between 50 and 69 receive a “C”; and scores below 50 receive an “F”.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Tanaka | 85 | =IF(B2>=90, “A”, IF(B2>=70, “B”, IF(B2>=50, “C”, “F”))) |
| 3 | Sato | 75 | =IF(B3>=90, “A”, IF(B3>=70, “B”, IF(B3>=50, “C”, “F”))) |
| 4 | Suzuki | 45 | =IF(B4>=90, “A”, IF(B4>=70, “B”, IF(B4>=50, “C”, “F”))) |
*For Excel 2016 and later, you can use the IFS function to simplify the formula:
=IFS(B2>=90, “A”, B2>=70, “B”, B2>=50, “C”, TRUE, “F”)
Steps
- Enter the following formula in cell C2:
- =IF(B2>=90, “A”, IF(B2>=70, “B”, IF(B2>=50, “C”, “F”)))
- This formula evaluates the score in B2 and returns the appropriate grade.
- Repeat the formula for other cells, such as C3 with =IF(B3>=90, “A”, IF(B3>=70, “B”, IF(B3>=50, “C”, “F”))).
- Each student’s grade will be displayed based on their score.
Alternative: Simplify Branching with SWITCH
From Excel 2019 onward, you can use the SWITCH function to handle multiple conditions more easily. SWITCH evaluates an expression and returns a result based on a matching condition. It avoids complex nested IF functions, making the formula easier to read and manage.
In the example below, grades are assigned based on scores:
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Tanaka | 85 | =SWITCH(TRUE(), B2>=90, “A”, B2>=70, “B”, B2>=50, “C”, “F”) |
| 3 | Sato | 75 | =SWITCH(TRUE(), B3>=90, “A”, B3>=70, “B”, B3>=50, “C”, “F”) |
| 4 | Suzuki | 45 | =SWITCH(TRUE(), B4>=90, “A”, B4>=70, “B”, B4>=50, “C”, “F”) |
Steps
- Enter the following formula in cell C2:
- =SWITCH(TRUE(), B2>=90, “A”, B2>=70, “B”, B2>=50, “C”, “F”)
- This formula uses the SWITCH function to evaluate conditions and return the corresponding grade.
- Repeat the formula for other cells to display grades for each student.
Summary
You can use multiple IF functions to create complex conditional branching in Excel. Additionally, starting with Excel 2019, the SWITCH function provides a simpler way to manage multiple conditions. Choose the method that best suits your needs.