How to Make a Nested IF Statement in Excel
A nested IF statement is an If statement within an If Statement. It can be a very powerful tool in Excel and can help you manipulate data in ways you might never have thought you could. However, it can be tricky to do if you aren’t familiar with them. I will show you an Easy Step by Step way to make one.
Before we do, let’s go over what an IF statement is. This is a logical function that gets asked a criteria that is either TRUE or FALSE. Then you assign it a TRUE result and a FALSE result.
ex. =IF(A1=”sunny”,”Lets go swimming”,”Its not sunny”)
The IF statement example above perfectly shows what an IF statement is. There are 3 sections in parenthesis. The first part is the criteria. That is asking if cell A1 equals the word “sunny”. Then the next part after that is the TRUE result. If that statement is TRUE, then Excel would put “Lets go swimming”. If it is FALSE, then it would put “Its not sunny”.
However, what if cell A1 contains a few possible answers and you want your IF statement to reflect that. Let’s say that A1 could also say “cloudy” or “Rainy” and you want to check for that too.
We could make a second IF statement next to it. It would look like”
=IF(A1=”cloudy”,”Get my jacket”,”It’s raining!”)
So now we have two IF statements both checking cell A1. The formulas are:
And the results are:
If we break these down quickly, the formula in B1 asks if A1 equals Sunny which it does not so it enters the FALSE result. For the second one, it asks if A1 equals cloudy which it does not, so it also gives the FALSE result.
This is very cluttered though and you don’t want results in two cells. It must
be in one cell. So how do we do that? You got it! With a Nested If statement.
If you are not good with writing out nested IF statements, then doing it this way will help. Write out the two IF statement next to eachother, like we did in cells B1 and C1.
Now we have to copy the second IF statement, and put it into the FALSE section of the first IF statement.
So we have the first IF statement below. The FALSE part is in hilighted with the arrow pointing to it.
So we are asking if cell A1 is sunny. If it is, we put the TRUE part of the formula and are done with it. If it is not sunny, then we want to ask the second question “is it cloudy?” So since we know it is not sunny, then we delete that FALSE part of the formula below and replace it with the second IF statement.
It should now look like this:
So what this does is :
1. Checks if cell A1 = “sunny”
2. If it does, then the result is “Lets go swimming”.
3. If it does not, then it asks the second question to see if cell A1 is “cloudy”
4. If it is cloudy, then it will give the result “Get my jacket”
So you have just created a Nested IF statement! Congratulations! Now you could add on to that and insert more If statements or even other functions into the TRUE or FALSE parts. But this example should help you explain how to do this with ease. Just make sure to make all your functions first in seperate cells and then copy and paste them into your Nested IF statement one by one and then test it to make sure it works!Source: excelbyjoe.com