How to Use Nested IF Function in Excel (6 Ideal Examples)

In this article, we will learn to use the nested IF function in Excel. When a function is inserted inside another function, then it is called a nested function. Today, we will show 6 ideal examples of the nested IF function. These examples will help you to understand the use of the nested IF function. So, without any delay, let’s start the discussion.


Download Practice Book

Download the practice book from here.


Introduction to Excel IF Function

To understand the nested IF function, we need to know about the IF function first. In Microsoft Excel, the IF function checks a given condition and if it meets then, shows a result. Also, if the condition is not met, then shows another result.

  • Syntax

IF (logical_test, [value_if_true], [value_if_false])

  • Argument

logical_test: It is the compulsory argument of the IF function. This argument represents the given condition for a cell or a range of cells.

[value_if_true]: It is the second argument of the function. It is the defined statement if the condition is met.

[value_if_false]: It’s the third argument that appears if the condition is false.


Introduction to Excel Nested IF Function

The nested IF formula holds multiple IF functions. In case of multiple conditions, we need to use the IF function inside another IF function. The general form of the nested IF function can be written as:

IF(C1,T1,IF(C2,T2,(IF(C3,T3,IF(C4,T4,F4))))

Here,

  • C1: First Condition.
  • T1: Value to be displayed if the first condition is met.
  • C2: Second Condition.
  • T2: Value to be shown if the second condition is met.
  • C3: Third Condition.
  • T3: Value to be displayed if the third condition is met.
  • C4: Fourth Condition.
  • T4: Value to be shown if the fourth condition is met.
  • F4: This is the value that will be displayed if conditions are not met.

You can add more conditions according to your needs inside the formula.


6 Ideal Examples of Using Nested IF Function in Excel

1. Use of Simple Nested IF Function to Find Results

To understand the use of the nested IF function, we will show a simple example in the first place. In this example, we will try to find the results of some students. Here, we are using three conditions.

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

Use of Simple Nested IF Function to Find Results

Let’s follow the steps below to see how the nested IF function works.

STEPS:

  • First of all, select Cell C5 and type the formula:
=IF(C5="","Absent",IF(C5>=70,"Pass","Fail"))
  • After that, press Enter to see the result.

Use of Simple Nested IF Function to Find Results

In this formula,

  • 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.
  • In the following step, put the cursor on the bottom right side of Cell D5 and drag the Fill Handle down.

Use of Simple Nested IF Function to Find Results

  • Finally, you will see results like the picture below.

Use of Simple Nested IF Function to Find Results


2. Find Grade Using Nested IF Function in Excel

In the second example, 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 that contains the marks of some students. Here, the range of marks and corresponding grades are also given. We need to evaluate the grades of the students based on their obtained marks.

Find Grade Using Nested IF Function in Excel

Let’s observe the steps below to learn more about this example.

STEPS:

  • In the first place, select Cell D5 and type the formula:
=IF(C5<61,"F",IF(C5<71,"D",IF(C5<81,"C",IF(C5<91,"B","A"))))
  • Then, press Enter to see the result.

Find Grade Using Nested IF Function in Excel

In this formula,

  • Our first condition is to check whether there is any mark below 61.
  • If TRUE, then it returns F.
  • If FALSE, then it checks the next IF
  • In the next IF function, it checks marks below 71 and returns D if it is TRUE.
  • In this way, the nested IF function moves on to check all the conditions.
  • After that, use the Fill Handle to see results in all cells.

Find Grade Using Nested IF Function in Excel


3. Apply Excel Nested IF Function to Allocate Vacation Days

We can use the nested IF function for more purposes too. In the third example, we will 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, then he will have 25 vacation days. If it is between 9 to 14 years, then he will have 15 vacation days. And lastly, if the employment period is less than 9 years, then he will have 10 vacation days.

Apply Excel Nested IF Function to Allocate Vacation Days

Let’s pay attention to the steps below to learn the example.

STEPS:

  • Firstly, select Cell D5 and type the formula below:
=IF(C5>=15,25,IF(C5>=9,15,IF(C5>=1,10,0)))
  • After that, hit Enter to see the result.

Apply Excel Nested IF Function to Allocate Vacation Days

In this formula, we have used 3 conditions.

  • In the first condition, we checked if Cell C5 is greater than 15. As it is TRUE, it shows 25 in Cell D5.
  • If it was FALSE, then, it would check the next condition and so on.
  • Lastly, drag the Fill Handle down to see results like the screenshot below.

Apply Excel Nested IF Function to Allocate Vacation Days


4. Determine Payment Status with Nested IF Function in Excel

In practical life, we need to determine the payment status often. Service-providing organizations need to keep the record of payments of their customers. In those cases, we can also use the nested IF function.

In the dataset below, we can see the Bill and Paid Amounts of some customers. Using this information, we will try to update the Status column.

Determine Payment Status with Nested IF Function in Excel

So, let’s follow the steps below to understand the example.

STEPS:

  • In the first place, select Cell G5 and type the formula:
=IF(F5=0,"Paid",IF(TODAY()<C5,"On","Due"))
  • Press Enter to see the result.

Determine Payment Status with Nested IF Function in Excel

In this formula,

  • We will first check if Cell F5 is equal to 0. If it is TRUE, then it will show Paid.
  • Otherwise, it will move to the second condition.
  • In 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, then, it will show Due.
  • And if the current date is less than the Due Date, then it will display On.
  • Finally, drag the Fill Handle down to see results in all cells.

Determine Payment Status with Nested IF Function in Excel


5. Insert Excel Nested IF Function to Return Correct Product

In this example, we will try to return the correct product of two numbers using the nested IF function. To explain the example, we will use the below dataset.

Insert Excel Nested IF Function to Return Correct Product

Let’s follow the steps below to understand the example.

STEPS:

  • To begin with, select Cell C5 and type the formula:
=IF(B5="15x12",180,IF(B5="14x19",266,IF(B5="20x25",500)))
  • And hit Enter.

Insert Excel Nested IF Function to Return Correct Product

In this formula,

  • The first condition is to check if Cell B5 is equal to 15×12. If TRUE, then, it will show 180. If it is FALSE, then, we will move to the next condition.
  • In the next condition, it will look for 14×19 and show 266 if it satisfies the condition.
  • And in the last condition, it will search the value of 2 0x25 and display 500 if it meets the condition.
  • After typing the formula, drag down the Fill Handle.

Insert Excel Nested IF Function to Return Correct Product

  • In the end, you will see results like the picture below.

Insert Excel Nested IF Function to Return Correct Product


6. Apply Nested IF with AND/OR Functions in Excel

In the last example, we will use the nested IF function with both the AND & OR functions. First, we will show the use of the OR function and then, we will implement the AND function.

To explain the example, we will use a dataset that contains information about the sales amount of March & April. We will distribute the Sales Commission based on their sales amount.

Apply Nested IF with AND/OR Functions in Excel

Let’s follow the steps below to learn more about the distribution of the commission.

STEPS:

  • In the first place, select Cell E5 and type the formula:
=IF(OR(C5>5500,D5>5500),12%,IF(OR(C5>=4001,D5>=4001),9%,IF(OR(C5>=2500,D5>=2500),5%,"")))
  • Then, hit the Enter key to see the result.

Apply Nested IF with AND/OR Functions in Excel

In this formula, we have 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, then it will display the assigned value. That means if you need to satisfy any one condition, then you should apply the OR function.

  • 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 %.
  • In the second condition, it checks if the sales amount is between 4001 to 5500. It prints 9 % in the Commission.
  • And the last condition is to check the sales amount between 2500 to 4000.
  • After that, use the Fill Handle to copy the formula down.

Apply Nested IF with AND/OR Functions in Excel

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

  • In the following step, we will insert the nested IF formula with the AND function.
  • For that purpose, select Cell E5 and type the formula:
=IF(AND(C5>5500,D5>5500),12%,IF(AND(C5>=4001,D5>=4001),9%,IF(AND(C5>=2500,D5>=2500),5%,"")))

Apply Nested IF with AND/OR Functions in Excel

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

  • Finally, drag the Fill Handle down to see all results.

Apply Nested IF with AND/OR Functions in Excel


Alternatives of Nested IF Function in Excel

It becomes difficult to implement the nested IF function if you have a lot of conditions. The formula may get bigger and any small mistake can lead you to incorrect results. To avoid the difficulties, you can use the alternatives of the nested IF function in Excel. Here, we will discuss 5 alternatives. You can use them according to your needs.


1. Use VLOOKUP Function

When you are using continuous ranges of numbers, then you can use the VLOOKUP function instead of the nested IF function. For this purpose, 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. In the dataset, we have a sales amount for each salesman and will try to allocate the commission.

Use VLOOKUP Function

Let’s follow the steps carefully to learn more.

STEPS:

  • Firstly, select Cell D5 and type the formula:
=VLOOKUP(C5,$C$13:$D$15,2,TRUE)
  • After that, press Enter.

Use VLOOKUP Function

Here, we have 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, so we used TRUE in the last argument of the formula.

  • Finally, drag the Fill Handle down to see all results.

Use VLOOKUP Function


2. Apply Excel IFS Function

The application of the IFS function makes the task of implementing multiple conditions very easy. The general form of the IFS function can be written as:

IFS(Test1,Value1,[Test 2],[Value 2]....)

To explain the use of the IFS function, we can use the previous dataset.

Let’s observe the steps below to know more.

STEPS:

  • To begin with, select Cell D5 and type the formula:
=IFS(C5>5500,12%,C5>=4001,9%,C5>=2500,5%,TRUE,"")
  • Then, hit Enter.

Here, 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.

  • In the following step, use the Fill Handle down to see all results.


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. The general form of the CHOOSE function is given below.

CHOOSE((Test 1)+(Test 2)+(Test 3),Value 1,Value 2,Value 3)

You can check more tests inside the formula if you want.

Let’s pay attention to the steps below for more.

STEPS:

  • In the beginning, select Cell D5 and type the formula:
=CHOOSE((C5>=2500)+(C5>=4001)+(C5>5500),5%,9%,12%)
  • After that, press Enter.

Here, you can see four arguments inside the CHOOSE function. In the first argument, we have placed all the conditions adding them with the plus (+) sign. Then, in the next arguments, we have placed the value of the results with respect to the position of the conditions. For example, the second argument denotes the result of the first condition. And so on.

  • In the end, drag the Fill Handle down.


4. Try Excel SWITCH Function

You can also use the SWITCH function as an alternative to the nested IF function. But, you need to remember one thing. You can use the SWITCH function when you need to deal 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 easily.

Follow the steps below to know more about this method.

STEPS:

  • First of all, type the formula below in Cell E5:
=SWITCH(D5,$D$13,$E$13,$D$14,$E$14,$D$15,$E$15,"")
  • Now, press Enter to see the result.

Here, 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 %.

  • In the next step, just drag the Fill Handle down.


5. Use CONCATENATE Function

The SWITCH function was introduced in Excel 2016. The older versions don’t have the SWITCH function. In that case, you can use the CONCATENATE function in place of the previous method.

Let’s discuss the steps below.

STEPS:

  • In the first place, type the formula below in Cell E5:
=CONCATENATE(IF(D5="A",5%,""),IF(D5="B",9%,"") & IF(D5="C",12%,""))*1
  • After that, hit Enter to see the result.

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

  • Finally, drag down the Fill Handle to see results in the desired cells.


Things to Remember

You need to remember the following things while working with the nested IF function in Excel.

  • You must maintain the proper order of tests and results when you are using the nested IF function.
  • In Excel 2007 2016, you can use a maximum of 64 conditions.
  • If your formula gets bigger because of the number of conditions, apply the alternative methods instead.

Conclusion

In this article, we have demonstrated 6 ideal examples of the ‘Use of Nested IF Function in Excel’. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the alternative methods to the use of the nested IF function. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo