Using multiple IF statements
Before beginning, if you are unfamiliar with a traditional Excel IF formula, I recommend reviewing the basics of the IF formula – review the basics of the if formula
Great, if you are reading this you are familiar with how to use a traditional Excel if then formula and you are here to learn how to use multiple if statements in the same formula. You’re in luck, Excel will allow for up to seven nested if statements!
Remember that the traditional IF formula follows the following logic:
Syntax: = IF( logical_test, [value_if_true], [value_if_false])
Multiple IF statements, sometimes referred to as nested if statements, are created by replacing the [value_if_true] and/or the [value_if_false] arguments with an additional, or nested, IF statement (as mentioned above this can be done up to seven times)
IF ( logical_test1 , [value_if_true1], IF ( logical_test2 , [value_if_true2] , [value_if_false2 ) )
I’ve placed the 1’s and 2’s in the formula and changed the color of the text to help differentiate the nested if from the original. Comparing the original formula syntax to the example above, you can see that the FALSE argument in the original if statement was replaced with an entirely new IF formula.
Perhaps a more practical example would be more helpful:
IF ( B3 = “apple” , “apple tree” , IF ( B3 = “banana” , “banana tree” , “neither apple or banana” ))
Let’s break this formula down into its parts:
- logical_test1 is B3 = “apple”
- value_if_true1 = “apple tree”
- value_if_false1 = another if statement!
- logical_test2 is B3 = “banana”
- value_if_true2 = “banana tree”
- value_if_false2 = “neither apple or banana”
Let’s go through the formula step by step following the same list above:
- Is cell B3 equal to apple, TRUE or FALSE?
- If the logical test is TRUE then return “apple tree”
- If the logical test is FALSE then start a new if statement;
- The new IF statement asks if cell B3 is equal to banana, TRUE or FALSE
- If the second logical test is TRUE then return “banana tree”
- If the second logical test is FALSE then return “neither apple or banana”
To open the Multiple IF Formula Example show below, click here: Multiple IF Statement Example