How to Use Nested IF Function in Excel: 7 Methods

Method 1 – Use Nested IF Function to Find Multiple Results Based on Criteria

  • If a student gets 70 or higher, then he will pass.
  • If he gets less than 70, then he will fail.
  • If there is no mark, the student will be considered absent.

Insert the following formula in cell D5.


 💡 Formula Breakdown

The first argument is C5=”” and the second argument is: Absent. It denotes the first condition. It indicates if Cell C5 is empty, then, it will show the second argument. In our case, that is Absent.

The second IF function states that if the mark is higher than 70, then a student will pass. Otherwise, he won’t.

Dragging the Fill Handle throughout column D will return the output for all of the students.

Simple nested IF function

2. Find Grade Using Nested IF Function

We will use the nested IF function in Excel to find the grades of some students. It is one of the most used examples to describe the nested IF function. For this example, we will use a dataset containing some students’ marks. The range of marks and corresponding grades are also given. We need to evaluate the grades of the students based on their obtained marks.

Apply the following formula in cell D5:


 💡 Formula Breakdown

  • The first condition is to check whether there is any mark below 61.
  • If TRUE, it returns F.
  • If FALSE, it checks the next IF.
  • In the next IF function, it checks marks below 71 and returns D if it is TRUE.
  • The nested IF function moves on to check all the conditions.

Nested IF function to find grade

Method 3 – Apply Nested IF Function to Allocate Vacation Days

Try to allocate a Vacation period for employees of a company. To allocate a vacation period, we have introduced some conditions. If the employment period of an employee is 15 years or more, he will have 25 vacation days. If it is between 9 to 14 years, then he will have 15 vacation days. If the employment period is less than 9 years, he will have 10 vacation days.

Apply the following formula in cell D5 to get the corresponding vacation days.


 💡 Formula Breakdown

In this formula, we used 3 conditions.

We checked if Cell C5 is greater than 15. As it is TRUE, it shows 25 in Cell D5.

If it was FALSE, it would check the next condition and so on.

Allocating vacation with nested IF function

Method 4 – Use Nested IF Function with Text Condition

We used the nested IF with cell reference. We may need to deal with conditions in text format. Consider the dataset stated below. We have a dataset of some customers who ordered different products of different quantities. For a specific condition, let’s say, if any customer orders more than 50 “Keyboard” at a single time, his order will be delivered. The text condition is “Keyboard”.


 💡 Formula Breakdown

The formula checks whether the cell value of C6 is Keyboard or not. If so, then it again checks the quantity in cell E6. If it is larger than 60, it returns “Delivered” as mentioned in the formula.

Nested IF function with text condition

Method 5 – Insert Nested IF with Logical Operator

5.1. Nested IF with OR Function

See the application of the OR function with the nested IF function.

Use a dataset that contains information about the sales amount for March & April. We will distribute the Sales Commission based on their sales amount.

Apply the following formula in cell E5.


 💡 Formula Breakdown

We used the nested IF function with the OR function. We can use multiple conditions inside the OR function. If any one of these conditions is TRUE, it will display the assigned value. That means you should apply the OR function if you need to satisfy any one condition.

The first condition checks if the sales amount in any of the two months is greater than 5500 and if TRUE, it sets the commission to 12 %.

It checks if the sales amount is between 4001 and 5500. It prints 9 % in the Commission.

The last condition is to check the sales amount between 2500 to 4000.

Nested IF with OR Function

Note: The Number Format of the range E5:E10 must be set to Percentage. Otherwise, it will show 0.

5.2. Nested IF with AND Function

Apply the AND function with nested IF now. In that case, the formula will be:

=IF(AND(C5>5500, D5>5500), 12%, IF(AND(C5>=4001, D5>=4001),9%,IF(AND(C5>=2500, D5>=2500), 5%, "")))

 💡 Formula Breakdown

Both conditions inside the AND function must be TRUE. Otherwise, it will execute the following IF condition. For example, if both Cell C5 and D5 are greater than 5500, it will only set the commission to 12 %.

Nested IF with AND function

Method 6 – Apply Nested IF in Conditional Formatting

  • Select the range of data >> go to Home tab >> click dropdown of Conditional Formatting >> select New Rule.

Apply conditional formatting

  • The New Formatting Rule dialog box will appear.
  • Select “Use a formula to determine which cells to format” from the Select a Rule Type field.
  • Use the formula below:

=IF($C5="Keyboard", IF($E5>50, TRUE, FALSE), FALSE)

  • Click Format.

Format values with nested IF formula

  • From the Format Cells dialog box, go to Fill icon >> select a color >> click OK.

Choose color to format cells

  • Close the New Formatting Rule dialog box by clicking OK.

New Formatting Rule dialog box

You will find that the rows containing “Keyboard” with a quantity of over “50” are highlighted.

Highlight cells applying conditional formatting with nested IF

Method 7 – Use TODAY Function with Nested IF Function to Determine Payment Status

We need to determine the payment status often. Service-providing organizations need to keep a record of payments of their customers. We can also use the TODAY function nested in the IF function.

We can see the Bills and Paid Amounts of some customers. Using this information, we will try to update the Status column.

Apply the formula below in the first cell (i.e. G5) of the “Status” column


 💡 Formula Breakdown

  • Check if Cell F5 is equal to 0. If it is TRUE, then it will show Paid. It will move to the second condition.
  • We used the TODAY function and compared it with the Due Date.
  • If the current date is greater than the Due Date, it will show Due.
  • If the current date is less than the Due Date, it will display On.

TODAY function with nested IF

Alternative Approaches to Nested IF Function in Excel

Method 1 – Use VLOOKUP Function

When you are using continuous ranges of numbers, you can use the VLOOKUP function instead of the nested IF function. You need to have a reference table and create the formula with the approximate match. In our case, the Commission table is our reference table. We have a sales amount for each salesman in the dataset and will try to allocate the commission.

Our formula will be:


We used the VLOOKUP function to look for the value of Cell C5 in the second column of the lookup table ranging from Cell C13 to D15. We need to apply the approximate match here, we used TRUE in the last argument of the formula.

Using VLOOKUP function

Method 2 – Apply IFS Function

The application of the IFS function makes the task of implementing multiple conditions very easy. For a similar dataset as above, our formula will be as below:


Test 1 is to check whether Cell C5 is greater than 5500. If TRUE, then it will show 12 %. Otherwise, it will move to Test 2 and so on.

Applying IFS function

Method 3 – Insert CHOOSE Function

We can also use the CHOOSE function to check multiple conditions. The CHOOSE function returns a value from the list based on the index number of that value.


 💡 Formula Breakdown

You can see four arguments inside the CHOOSE function. We placed all the conditions adding them with the plus (+) sign. We placed the value of the results concerning the position of the conditions. The second argument denotes the result of the first condition. And so on.

Using CHOOSE function

Method 4 – Apply SWITCH Function

You can also use the SWITCH function as an alternative to the nested IF function. You need to remember one thing. Use the SWITCH function when dealing with a fixed set of specific values. In the dataset, you can see we have introduced Rank in place of the Sales Range. These specific values of Rank will help us to distribute the commission efficiently.

We have used the following formula in cell E5.

=SWITCH(D5, $C$13, $D$13, $C$14, $D$14, $C$15, $D$15,"")

The formula will look for the value of Cell D5. If the value is A, then it will print 5 %, if B then 9 %, and if C then 12 %.

Applying SWITCH function

Method 5 – Use CONCATENATE Function

The SWITCH function was introduced in Excel 2016. The older versions don’t have the SWITCH function. You can use the CONCATENATE function in place of the previous method. Apply the formula in cell E5.

=CONCATENATE(IF(D5="A", 5%, ""),IF(D5="B", 9%, "") & IF(D5="C", 12%, ""))*1

We concatenated multiple IF functions. This formula shows 5 % if the value of Cell D5 is A, 9 % if B, and 12 % if C.

Using CONCATENATE function

Frequently Asked Questions

1. What is the limit of nested IF in Excel?

The maximum number of nested IF functions in Excel depends on the version of Excel you are using.

In Excel 2016 and later versions, the maximum number of nested IF functions is 64. This means you can nest up to 64 IF functions within a single formula.

In earlier versions of Excel, such as Excel 2013 and earlier, the maximum number of nested IF functions is 7.

2. What are the disadvantages of nested IF function?

The formula gets complicated with the application of a lot of IF blocks. It limits flexibility and increases the calculation time compared to other functions like VLOOKUP or SWITCH.

Takeaways from this Article

  • The nested IF function allows you to perform more complex calculations in Excel by nesting multiple IF statements within each other.
  • To use the nested IF function, you need to specify the logical test or condition that you want to evaluate, as well as the value or action to take if the condition is true or false.
  • Using conditional formatting with nested IF allows highlighting cells which is more helpful in practical scenarios.
  • You can combine the nested IF function with other functions in Excel, such as the AND and OR functions, to create even more complex logic and calculations.

Things to Remember

  • When nesting IF functions, it’s important to ensure that each condition is properly nested and that you use the correct syntax to avoid errors.
  • When using nested IF functions, it’s important to use parentheses to group each condition and its corresponding result.
  • Consider other functions, such as the VLOOKUP or SWITCH functions, to see if they can simplify your formula.

Download Practice Book

Download the practice book from here.

<< Go Back to Nested Formula | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF