Multiple IF Statements In Excel – Nested IF Functions Explained
The IF function is an extremely powerful tool that gives you the ability to manipulate and analyze your Excel data based on conditions. This statement stems from the logical use of “IF” to base the value of one cell off of conditions that exist in one or more other cells.
We use the word “if” in everyday life to make decisions in the same way that Excel uses the IF function to make decisions based on your data. In real life, for instance, we may decide that “if” we get a raise, we will take a vacation. This statement relies on us evaluating the condition and then taking action based on that evaluation in the same way that Excel examines a condition and then takes further steps based on its assessment.
Excel gives you even more power over the data in your spreadsheets by allowing you to use multiple IF statements in the same expression. This tutorial will show you how to use them in your worksheets. To get the maximum value from this tutorial, you first need to know how to use the basic Excel functions and features.
The Basic IF Function in Excel
The basic IF function in Excel evaluates a condition and then performs a number of steps based on the result of that evaluation. Look at the chart below to see a visual representation of the logic behind the IF function.
As the image above suggests, we use the IF statement to evaluate a condition. If the statement returns true, then one value is returned. If the statement returns false, then another value is returned. Let’s look at the syntax of the IF statement.
Microsoft Excel IF syntax
Here is the syntax of the IF statement in Excel:
IF(condition, value_if_true, value_if_false)
Here are the details on the parameters:
• condition: The value that you want to test.
• value_if_true: The value that is returned if condition evaluates to TRUE.
• value_if_false: The value that is returned if condition evaluates to FALSE.
To get a sense of how this works, we can convert it to pseudocode like the example below:
IF (condition) THEN value_if_true ELSE value_if_false END IF
The third parameter in the Excel IF statement is equivalent to what an ELSE statement would return in many programming languages, but you can also use another IF statement as the third parameter. This structure means that you could create an IF statement, and then if that statement evaluates to true, the code can then use another IF statement and so on.
Here is what a nested IF statement would look like:
IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false2))
We can look at this as pseudocode again to figure out what is happening.
IF condition1 THEN value_if_true1 ELSEIF condition2 THEN value_if_true2 ELSE value_if_false2 END IF
With the current version of Excel, you can nest up to 64 different IF functions — which is basically like chaining a bunch of ELSEIF conditions in a programming language. Note, though, that just because it’s possible to nest a large amount of IF statements, doesn’t mean it’s a good idea. We’ll explain the reason behind that later in the article, but for now, let’s look at some examples of how nested IF statements can help you perf data analytics.
How to Use Multiple IF Statements in Excel
Before we get into using multiple IF statements, let’s get started with a simple example of using only one IF statement and build from there.
Using the IF Statement in Excel
For our following examples, imagine you’re a teacher who needs to assign a grade to each student based on their test scores. You have the names of the students and their test scores in a spreadsheet. Here is an sample of the type of data you will have:
Let’s be lenient with the students and say that any student getting over 50% of the questions right passes. Below, you can see what our data looks like in an Excel spreadsheet. We also added a Pass/Fail column for the IF function we will be using.
After you have your data in the spreadsheet, select the top cell in the Pass/Fail column and click on the Fx button to add a function to the cell. This is the function that will print “Pass” or “Fail” in the column:
IF(B2 > 50, "Pass", "Fail")
You can add that behind the equals sign. This basically says if the value in the B2 cell is greater than 50, then print “Pass.” If not, print “Fail.” To add the function to the rest of the cells in the column, just highlight and drag the cell down the column, and it will create functions for each cell. If you click on them, you will notice the only thing that changes is the cell value in the function. The following function in the next cell down will be this:
IF(B3 > 50, "Pass", "Fail")
That was a simple example, but it shows how valuable the IF function can be in Excel. Imagine having to determine a pass or fail in a list of a hundred students just by looking at the Test Score column. Let’s take this a little further and give the students letter grades instead of simply passing or failing them. This will give us a chance to use nested IF statements.
Using a Nested IF Statement in Excel
You have decided that pass and fail are not enough. You need to assign letter grades to the students. Here is how you will determine that. If a student gets a test score under 50, then they get an “E” grade. If a student scores between 50 and 60, they get a “D.” If the score is between 60 and 70, they get a “C.” With a score between 70 and 80, they get a “B,” and with a score of 80 or more, they get an “A.”
This is what our new student spreadsheet will look like:
We will use the IF statement syntax to create the various conditions needed to assign the different grades required.
Essentially, we will create an “IF” statement that checks if the test is 50 or lower. If the condition is true, we will assign a grade of “E” to that student. If the condition is false, we will use a new IF statement to create a new condition that checks if the grade is between 50 and 60. If this condition is true, it will assign a “D” grade, but if the condition is false, we will need to create a new IF statement to check the new conditions. In this way, we will create multiple IF statements to check for all the conditions required to assign the student’s correct grade.
This is what the Excel formula for checking each condition using the multiple IF statements looks like:
IF(B2 < 50, "E", IF(B2 < 60, "D", IF(B2 < 70, "C", IF(B2 < 80, "B", IF(B2 < 100, "A")))))
Multiple IF statements in Excel can be hard to create and can become incredibly complex to follow. A good rule to follow when creating multiple IF statements is to write the statement in plain English first. This will help you create a logical structure that you can use to create your Excel IF statement. Another option is to use pseudocode, as was done in the syntax section. For our nested IF formula, it would look like this:
IF B2 < 50 THEN "E" ELSEIF B2 < 60 THEN "D" ELSEIF B2 < 70 THEN "C" ELSEIF B2 < 80 THEN "B" ELSEIF B2 < 100 THEN "A" END IF
The resulting worksheet will look like this:
Avoiding Issues with Multiple IF Statements
Microsoft Excel will allow you to nest up to 64 IF statements, but you really wouldn’t want to do that. Reasons for this include:
• Multiple IF statements take some work to create. It is hard to remember what you are doing when you are nesting a lot of IF statements. It was hard to tell what our IF statement was doing to calculate the letter grades, and there were only four nested IF statements there. Imagine if there were 64.
• Multiple IF statements can be hard to maintain. You may know how they work when you initially create them, but what about if you have to come back months later to edit them? And what if someone else gets the job of modifying them? It will take time to determine what you were trying to do in the first place.
• Even though you can use 64 nested IF functions in Excel versions created after 2007, older versions of Excel don’t allow that much nesting. Excel 2003 only supports seven nested IF functions.
If the IF statements in your Excel spreadsheet are too long, it might be time to rethink how you are creating your spreadsheet. Reorganizing the data and using other Excel functions like SWITCH may be the answer.
Alternatives to Nested IF Statements
Multiple IF statements can give you a lot of power, but they can get out of hand. There are quite a few Excel formulas that can replace multiple nested IF statements in the right situation. Let’s look at a few of the options.
The SWITCH Function
The SWITCH function would not have worked for the letter grades spreadsheet we have because we are using a range as our conditions. However, the SWITCH statement can function as a concise form of the nested IF statement for pre-defined values. If, for example, we wanted to base another column in the spreadsheet off of the letter grades, a SWITCH statement would be perfect for the job. Let’s say that we want to assign each student to a room number based on their letter grades. If we wrote a nested IF statement to do this, it would look like this:
IF(C2 = "A", 100, IF(C2 = "B", 101, IF(C2 = "C", 102, IF(C2 = "D", 103, IF(C2 = "E", 104)))))
Because we are looking for a specific value in the IF function, we can replace this with a SWITCH function. The syntax of the SWITCH function is the following:
SWITCH(expression, value1, result1, value2, result2, …, [default])
The SWITCH function evaluates the value in the expression parameter. If it matches value1, then result1 is returned. If it matches value2, then result2 is returned, and so on. So to replace the nested IF statement above, we would use the following SWITCH statement:
SWITCH(C3, "A", 100, "B", 101, "C", 102, "D", 103, "E", 104)
This statement checks the C3 cell, and if it contains “A,” then we set the value to 100. If it is “B,” we set it to 101, and so on. The SWITCH function made its debut in Excel 2016. For older versions of Excel, you can concatenate multiple IF statements.
Concatenating Multiple IF Functions in Excel
To do something similar to the SWITCH statement in versions of Excel that released before 2016, you can concatenate multiple IF statements using the ampersand or CONCATENATE function instead of nesting them. This only works where a SWITCH statement would work. The values we are evaluating have to be pre-defined and not a range. The following expression could replace the SWITCH statement above:
(IF(C2 = "A", 100, "") & IF(C2 = "B", 101, "") & IF(C2="C", 102, "") & IF(C2="D", 103, "") & IF(C2="C", 104, ""))
We could also replace it with this:
CONCATENATE(IF(C2 = "A", 100, ""), IF(C2 = "B", 101, ""), IF(C2="C", 102, ""), IF(C2="D", 103, ""), IF(C2="C", 104, ""))
These statements are still pretty lengthy, but they aren’t nested, making them simpler to read.
The IFS Function
Another feature of Microsoft Excel 2016 and later versions is the IFS function. You can use it to evaluate multiple conditions. Here is the syntax of the IFS function:
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]...)
This syntax is like the SWITCH function’s syntax, but the IFS function allows ranges. If the logical test evaluates to true, then the formula will return the matching value. We can use it to replace the expression we used initially to add the letter grades to the grades column. Here is that expression again:
IF(B2 < 50, "E", IF(B2 < 60, "D", IF(B2 < 70, "C", IF(B2 < 80, "B", IF(B2 < 100, "A")))))
We can create a formula like this to replace it:
IFS(B2 < 50, "E", B2 < 60, "D", B2 < 70, "C", B2 < 80, "B", B2 < 100, "A")
The CHOOSE Function
You can also use the CHOOSE function to replace multiple IF statements in your Excel spreadsheets. The syntax of the CHOOSE function is a little more advanced than the functions we have covered so far. Here is that syntax:
CHOOSE(index_num, value1, [value2], [value3], [value4], ...)
The value of the index_num determines the return value. If the index_num is 1, then it returns value1. If the index_num is 2, then it returns value2, and so on. Here is how to do it for our letter grade formula:
CHOOSE((B2 < 60) + (B2 < 70)+ (B2 < 80)+ (B2 < 90)+ (B2 < 100), "A", "B", "C", "D", "E")
This expression works because TRUE = 1 and FALSE = 0 in Excel. So if a student got a 95, only the B2 < 100 expression would evaluate to TRUE. All the rest would be FALSE. This would make the index_num equal to 1 + 0 + 0 + 0 + 0 or 1. This would evaluate the value in the value1 parameter or “A.” If a student got a 50, then all the expressions in the index_num parameter would add up to 5, which results in an “E.”
The IF function is a powerful tool you can use in your Excel spreadsheets. You can use it for data analysis, conditional labeling of data, and more. The fact that you can nest IF statements gives you even more control over the conditionals in your spreadsheets, along with the ability to compare more than two values against each other. Nested IFs can become hard to manage, but there are Excel functions you can use in place of multiple IF statements, including SWITCH, IFS, and CHOOSE. Nonetheless, you still can’t beat the IF statement for flexibility.
Top courses in Excel
Excel students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.