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.

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.

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.

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

### 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.

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.

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.

### 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.

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.

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.

### 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.

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.

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.

### 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.

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**.

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**.

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

### 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.

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.

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.

**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%,"")))`

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.

## 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.

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**.

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.

### 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.