How to Use Multiple IF Statements in Microsoft Excel
Found This Helpful
Use multiple IF statements in a single Excel formula by nesting them inside parentheses.
In programming languages, IF statements are used to evaluate a condition and perform either one of two actions, depending on whether the condition is met. For example, Web applications use IF statements to evaluate whether the text a user enters into a form is a valid email address. The application then either does nothing or displays a "Please enter a valid email address" message, based on the evaluation of the condition.
While IF statements exist in all modern programming languages, they can also be used in Excel to insert one of two possible values into a cell, depending on whether the condition evaluates to true or false.
The basic structure of an IF statement in an Excel formula is:
=IF(Condition, Value if condition is true, Value if condition is false)
After testing the condition, Excel returns either one of the two supplied values, based on whether the condition evaluated to true or false.
For example, the following IF statement would return "Yes" if the A1 cell contains the word "Banana" and "No" if it contains any other value:
Unlike most programming languages, Excel uses a single equals sign in conditional statements to check whether two values are identical.
Multiple IF Statements
Multiple IF statements let you evaluate a condition that has more than two possible outcomes. To create a multiple
IF statement, replace the value returned if the condition is evaluated to false in the first statement by a second IF statement, enclosed in its own set of parentheses.
For example, this formula containing two IF statements checks whether the person whose age -- expressed in years -- in cell A2 is a child, a teenager or an adult:
(Image: Image courtesy of Microsoft)
The statement executes as follows:
- Excel evaluates whether the age entered in cell A1 is lower than 13. If it is, the "Child" value is assigned to the cell that holds the formula, and Excel does not execute the rest of the statement. If the age is equal to 13 or greater, the condition evaluates to false and the second IF statement executes.
- Excel supports up to 64 nested IF statements in a single formula, allowing you to evaluate up to 64 different outcomes.
- Create complex conditional statements by using logical operators in your IF statements.
- Depending on the condition you want to evaluate and the number of nested statements in your formula, the VLOOKUP function may be easier to manage than multiple IF statements.