Nested/Multiple IF Statements in Excel: Function, Example, and How to Use

In this tutorial, you will learn completely about nested/multiple IF statements in excel.

Before learning this tutorial, you better know the basic of IF formula usage in excel first. If you haven’t known it yet, then you can learn this tutorial here.

When processing data in excel, we sometimes need to get a data result based on multi-level logic conditions. That means when we evaluate the first condition and it is false, then we evaluate the second condition. If it is false again, then we evaluate the third condition and so on.

We can hop through those logic conditions if we master the way to use multiple IF statements in excel. Want to learn how to use that so you can get an optimal result from the logic conditions that you have? Read all parts of this Compute Expert tutorial!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

What are Nested/Multiple IF Statements?

By definition, we can actually differentiate between nested multiple IF statements and multiple IF statements. However, people mostly refer to one similar definition when it comes to both of them.

In this tutorial, we will focus our discussion on this similar definition. This is because this definition is the one most people use (and get confused about) when they work in excel.

However, if we define them differently, multiple IF statements are the excel formula writing where we write IF in another IF. For example, we can say that these formula writings below are forms of multiple IF statements.





In the first writing, we put an IF in the TRUE input part of another IF. In the second one, however, we put the IF in the logic condition input part of another IF. We can call both multiple IF statements as they consist of multiple IFs in one formula writing.

Multiple IF statements can also be the condition where we put an IF in the FALSE part of another IF. This means if the logic condition we test in our first IF is false, then we will go to another IF.

You can see the example of these multiple IF statements below.



This kind of multiple IF statements can have several continuous IFs as we can keep chaining the IF processes (we can put another IF in the false part of the second IF too and so on).

This is what we call nested IF statements and it is the multiple IF statements form that most people use. This is also what people usually refer to when they talk about multiple IF statements.

Thus, when we say the multiple IF statements or nested IF statements term later, this IF form is what we mean.

The Function of Nested/Multiple IF Statements in Excel

We use nested/multiple IF statements in excel to do a multi-level evaluation of logic conditions. We can get a different result depending on which logic condition level we get our TRUE.

Nested/Multiple IF Statements Result

We get a result from nested IF statements that depend on which logic condition level gives us a TRUE value.

Excel Version from Which We Can Use Nested/Multiple IF Statements

We can use multiple IF statements since we can use the IF formula in excel, which is from excel 2003. However, the limit of number of IFs you can use is different for excel 2003-2007 and excel post 2007.

Excel 2003 and 2007 allow you to nest up to 7 IFs in a single formula writing. Meanwhile, excel 2010 and above allows you to nest up many more, up to 64 IFs.

The Way to Write It and Its Inputs

Here is the general writing form of nested/multiple IF statements in excel.



We write the logic condition we want to evaluate first with its TRUE result in a more outer IF. Keep writing IF in the false part of the previous IF until we have inputted all logic condition levels we have (remember the 7 or 64 number of IFs limit, though). Input the result if all the logic conditions are false too.

Remember to give brackets at the end of the formula writing according to the number of IFs we have.

Example of Its Usage and Result

To better understand the nested IFs usage concept in excel, here is its implementation example.



In this example, we want to determine the bonus we give to some customers that buy a product. As we have different bonuses for different products, we can use nested IF statements to determine it.

We input a different pair of products and bonuses in each IF. There, we evaluate the product the customer buys and the bonus result if the evaluation produces true.

As we have three pairs of products and bonuses, we write the appropriate number of IFs to cover them all. If the customer doesn’t buy any product that gives a bonus, then we ask our nested IFs to produce “None”. This means we will give no bonus for the purchase.

We write multiple IFs that fulfill the requirements we have for the appropriate results we want for each customer. As a result, we can get what bonus we need to give to each customer fast!

Writing Steps

After we discussed the writing form, inputs, and an example of multiple IFs, we will discuss its writing steps next. This should be not too hard for you to follow if you have understood the previous parts of this tutorial.

1. Type an equal sign ( = ) in the cell where you want to put the nested/multiple IF statements result in
   
   
   
   
2. Type IF (can be with large and small letters) and an open bracket sign after =
   
   
   
   
3. Input the first logic condition you want to evaluate and type a comma sign ( , )
   
   
   
   
4. Input the cell content if the logic condition is true and type a comma sign
   
   
   
   
5. Repeat steps 2-4 for the next logic condition levels and their true results
   
   
   
   
6. Input the cell content if all of your logic conditions are false
   
   
   
   
7. Type close bracket signs. The number of close bracket signs you type must be the same as the number of IFs you have
   
   
   
   
8. Press Enter
9. Done!
   
   
   
   

Nested/Multiple IF Statements for Multiple Conditions Range

In terms of logic condition leveling, we often meet a situation when we have number ranges as the logic conditions. If our number falls into range A, then the result is this, and if range B, then the result is that. We can have several ranges with different results and we need to accommodate them all to get the correct result.

If we process this situation in excel, then this is when we should use nested IF statements to help us.

The general writing form of our nested IFs for this multiple conditions range is as you can see below.



The range condition inputs there mean you compare the number you have with the upper/lower limit of the ranges. Start from the lowest/highest range if you can so you don’t have to write an AND formula there. If the range condition is true, then give the result you want for that range.

Use the number of IFs appropriately according to the number of ranges you have for your logic conditions.

To better understand the nested IFs for multiple conditions range, here is the explanation implementation example.



Here, we want to assess salespeople according to the number of products they sold. As we have the guidance for the assessment result, we write multiple IF statements that can accommodate it.

We input the logic condition to our IFs according to the range conditions we have for the number of products sold. We also input the TRUE result if the number of products sold by the salesperson fits the range.

In this example, we start from the highest range, more than 2000, which will produce an excellent assessment result. From there, we go down towards smaller ranges.

As numbers more than 2000 have already been filtered, we just need to write logic conditions for the ones that haven’t. Thus, we write >1000 for the next logic condition. This number filter continues to go down until the last logic condition that we write.

Write the range conditions correctly to catch all the numbers in our nested IFs. As a result, we get the assessment results from all salespeople correctly and fast!

Nested/Multiple IF AND/OR Statements

Need to have some logic condition inputs where you evaluate multiple logic conditions simultaneously in your nested IFs? For that, you need to input AND/OR formulas to the logic condition input which has that situation.

If you have already understood how to combine IF with AND/OR, then this will be easy. You just need to input all the logic conditions you need to evaluate simultaneously to AND/OR. Separate those logic conditions with commas.

To choose correctly between AND/OR, remember that AND will produce TRUE if all the logic conditions are TRUE. Meanwhile, OR will produce TRUE if at least one of the logic conditions is TRUE.

Here is the general writing form for the combination of multiple IF statements with AND/OR formulas.



That writing assumes you need to evaluate multiple logic conditions in all your IFs. However, you only need to write AND/OR formulas for the IF which needs to evaluate multiple logic conditions.

Here is the implementation example of nested IFs with AND/OR formulas.



In the example, we need to assess sales results based on the assessment guidance we have. The assessment consists of two product sales and we need to simultaneously evaluate both numbers for two possible assessment results. To make the process faster, we use nested IFs with AND/OR formulas.

As an assessment result needs the two product sales numbers to fulfill certain conditions, we use AND to evaluate them simultaneously. This is because both product sales numbers must be true before we can say they are good or ok.

We input the appropriate sales numbers with their logic condition evaluations to our AND. We use AND in each IF because each assessment result requires logical conditions evaluations of both sales numbers.

We write the nested IFs with AND correctly and we can immediately get all the assessment results we need!

Exercise

After you understand how to use multiple IF statements in excel, now is the time you do an exercise! This exercise should help to sharpen your understanding!

Download the exercise file and answer all the following questions. Download the answer key file if you have done the exercise and want to check your answers. Or probably when you are confused about how to answer and want to see the correct answers to help you!

Link of the exercise file:
Download here

Questions

1. What is the score letter for each student in test 1?
2. What is the score letter for each student in test 2?
3. What is the score letter for each student in test 3?

Link of the answer key file:
Download here

Additional Note

It is not advisable to use too many IFs in one writing. Doing that can make it difficult to troubleshoot your formula writing if there is any problem.



Related tutorials you should learn: