What is Multiple IF Statements?
Multiple IF statements are also known as “Nested IF Statement” is a formula containing 2 or more IF functions. A single IF function only analyze two criteria. If there are more than two criteria, then it should use the multiple IF statements (nested IF). The number of IF functions required in multiple IF statements is the number of criteria minus 1.
For those who do not understand yet a single IF function, please read the following article
Multiple IF Statements in Excel
What is the weight category of each name below based on BMI value? to get the value of BMI, divide the weight (in KG) by the height (in Meter2)
The following is a weight category based on BMI value.
There are six criteria means it takes 5 IF function to assemble multiple IF statements.
IF Function #1 – Underweight or Not
The first IF function looking for “Who has Underweight Body and Who is Not.”
Here are the steps to write the first IF function.
- Place the cursor in cell E2
- Type the formula for the IF Function
- logical_test argument, analyze whether the BMI value is less than 18.5?, type D2<18.5
- value_if_true argument, type “Underweight”
- value_if_false argument, type “” (blank space)
Look below for the resulting formula
=IF(D2<18.5,"Underweight","")
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
The result is two names in “Underweight” category; the other ten names are not in the underweight category. There are five possibilities; it could be Normal, Overweight, Class I Obesity, Class II Obesity or Class III Obesity.
Who has the ideal body? Second IF function answer the question.
IF Function #2 – Normal Weight or Not
The second IF function fills the value_if_false argument of the first IF function. The second IF function is searching “Who has Normal Weight Body and Who is Not.”
Here are the steps to write the second IF function.
- Edit the formula in cell E2 by pressing F2 key
- Change value_if_false argument of first IF function from “” to the second IF function
- logical_test argument, analyze whether the BMI value is less than 25, D2<25
- value_if_true argument, type “Normal”
- value_if_false argument, type “” (blank space)
Look below for the resulting formula
=IF(D2<18.5,"Underweight" ,IF(D2<25,"Normal",""))
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
Two names in “Normal Weight” category and there are already four names and their weight category.
There are eight other names with no weight category. There are four possibilities; it could be Overweight, Class I Obesity, Class II Obesity or Class III Obesity.
Who has the overweight body? Third IF function answer the question.
IF Function #3 – Overweight or Not
The third IF function takes the value_if_false argument of the second IF function, seeking “Who has Overweight Body and Who is Not.”
Here are the steps to write the third IF function.
- Edit the formula in cell E2 by pressing F2 key
- Change value_if_false argument of second IF function from “” to the third IF function
- logical_test argument, analyze whether the BMI value is less than 30, D2<30
- value_if_true argument, type “Overweight”
- value_if_false argument, type “” (blank space)
Look below for the resulting formula
=IF(D2<18.5,"Underweight" ,IF(D2<25,"Normal" ,IF(D2<30,"Overweight","")))
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
Two additional names appear in “Overweight” category. There were six names known their weight category; there are still six other names not yet known. There are three possibilities left; it could be Class I Obesity, Class II Obesity or Class III Obesity.
Who has the class I obesity body? Fourth IF function answer the question.
IF Function #4 – Class I Obesity or Not
The fourth IF function occupies the value_if_false argument of the third IF function. The fourth IF function seeking “Who has a Class I Obesity Body and Who is Not.”
Here are the steps to write the fourth IF function.
- Edit the formula in cell E2 by pressing F2 key
- Change value_if_false argument of third IF function from “” to the fourth IF function
- logical_test argument, analyze whether the BMI value is less than 30, D2<35
- value_if_true argument, type “Class I Obesity”
- value_if_false argument, type “” (blank space)
Look below for the resulting formula
=IF(D2<18.5,"Underweight" ,IF(D2<25,"Normal" ,IF(D2<30,"Overweight" ,IF(D2<35,"Class I Obesity",""))))
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
Again, two additional names appear, but in “Class I Obesity” category. There were eight names known their weight category; there are four names remain unknown. There are remaining two possibilities; it could be Class II Obesity or Class III Obesity.
Who has the class II obesity body? Fifth IF function answer the question.
IF Function #5 – Class II Obesity or Class III Obesity
The fifth IF function, the last IF function to determine weight category for each name, it takes the value_if_false argument of the fourth IF function, looking for “Who has a Class II Obesity Body and Who is Not.”
Here are the steps to write the fifth IF function.
- Edit the formula in cell E2 by pressing F2 key
- Change value_if_false argument of fourth IF function from “” to the fifth IF function
- logical_test argument, analyze whether the BMI value is less than 30, D2<40
- value_if_true argument, type “Class II Obesity”
- value_if_false argument, type “” (blank space)
Look below for the resulting formula
=IF(D2<18.5,"Underweight" ,IF(D2<25,"Normal" ,IF(D2<30,"Overweight" ,IF(D2<35,"Class I Obesity" ,IF(D2<40,"Class II Obesity","")))))
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
There are still two names and unknown weight category, all if function already used. “Who has a Class III Obesity Body,” which if function answer this question?
If only one weight category left, then the last one takes the value_if_false argument of the fifth IF function.
Edit the formula in cell E2, change the value_if_false argument of fifth IF function from “” to “Class III Obesity.”
Look below for the resulting formula.
=IF(D2<18.5,"Underweight" ,IF(D2<25,"Normal" ,IF(D2<30,"Overweight" ,IF(D2<35,"Class I Obesity" ,IF(D2<40,"Class II Obesity","Class III Obesity")))))
Do a copy in cell E2, then do a paste in range E3:13. The results are as shown below.
Formula Challenge!!!
All IF functions above use the “<” operator. Is it possible to use “>”, “<=” or “> =” operator for multiple if statement above?
The result should not be different.
Video Tutorial for Multiple IF Statements
Here is a video explains step by step writing multiple IF statements one by one from the first IF function until the fifth IF function
The next video explains how to write all the IF functions at once
Another Alternative for Multiple IF Statements
IFS Function
The IFS function is available if you have an Office 365 subscription
, make sure you have the latest version of Office 365. Microsoft provides the IFS function as an alternative and an improvement of nested IF, no need to use multiple IF statements to analyze more than two criteria.
With the same example above, what is the weight category of each name below based on BMI value? Here is a solution using the IFS function. Look below for the resulting formula.
=IFS(D2<18.5,"Underweight" ,D2<25,"Normal" ,D2<30,"Overweight" ,D2<35,"Class I Obesity" ,D2<40,"Class II Obesity" ,TRUE,"Class III Obesity")
It makes no difference, multiple IF statements result and IFS function result.
For a more detailed explanation about the IFS function, please read the article below
VLOOKUP Function
The VLOOKUP function instead of multiple IF statements? Yes, you are not wrong, you can use VLOOKUP function to analyze more than two criteria. The key is the TRUE value for the range_lookup argument.
Using the same example as the previous case, what is the weight category of each name below based on BMI value?. Here is a solution using the VLOOKUP function. Look below for the resulting formula.
=VLOOKUP(D12,$A$3:$C$8,3,TRUE)
What is the difference? The multiple IF statements vs. the VLOOKUP function result.
For a more detailed explanation about the VLOOKUP function, please read the article below
Which One is the Best to Analyze more than 2 Criteria
Multiple IF Statements vs. IFS Function vs. VLOOKUP Function, which one is your choice?
Multiple IF statements tend to be difficult to use for the inexperienced, especially more than 5 IF functions in a formula. The biggest weakness of nested IF is there is a maximum limit. Before Excel 2007, seven is the maximum number in one formula, after Excel 2007 you can use up to 64 IF functions in one formula.
Although the latest version of Excel can accommodate a lot of IF functions, multiple IF statements are not the best solution, try to avoid it as much as possible.
The IFS function is an improvement for multiple IF statements provided by Microsoft, able to analyze up to 127 criteria. The disadvantage appears if there are any additional criteria; all formula must be edited to accommodate the addition of new criteria.
What about the VLOOKUP function? What is the maximum number of criteria allowed to be analyzed? If there is a new criterion added, should the formula be edited?
There are no maximum criteria for the VLOOKUP function; the number of rows in the excel worksheet is the limitation.
Look at the VLOOKUP function above, a VLOOKUP function and a table answering all the questions. If there are new criteria, then add the criteria in the table, by using the dynamic named range, no need to change the formula at all.
For me VLOOKUP is my choice, what is yours?
