# Beginner to Advanced IF Function in Excel-9 Examples

Among the logical functions in Excel, the **IF function** is the most commonly used. It is extremely rare for an Excel user, whether novice or advanced, to never use the **IF function**.

In a nutshell, the **IF function** returns custom values that correspond to multiple conditions. When the **IF function** is nested or embedded with other functions in Excel, its applications expand.

In this article, we will learn several ways to use the **IF function** with examples that will be useful for both beginners and advanced users of **MS Excel**.

**Syntax of IF Function**

`=IF(logic, return1, return2)`

**Argument of IF Function**

*logic-** logical expression or value to be evaluated*

*return1**-value to return if the ‘’logic’’ is satisfied.*

*return2**-value to return if the “logic” is not satisfied.*

*return1** and **return2** can be text or any numerical value.*

**Return of IF Function**

The **IF function** operates based on **TRUE** or **FALSE** returns from the logical argument. You can set the customized value to return based on the logical test’s **TRUE** or **FALSE** results.

According to the syntax of the **IF function**, the returns of the **IF function** are given below.

**9 Examples of Basic to Advanced IF function in Excel**

Before Jumping to examples you should know about the **logical operators** in Excel

**Example #1: Simple Application of IF Function**

Let’s try a simple example of the application of the **IF function**.

Assume you are given the following information: the student’s name and their English test scores.

You want to write a formula to return if the obtained score is** greater** than** 50** then **“Passed**”

You can simply apply the **IF formula** as below.

`=IF(C3>50,"Passed","Failed")`

- Input the above formula in the output cell
**D3** - Press the
**Enter** **AutoFill**the resting cells in column**D**

**Explanation:**

- According to the syntax of the
**IF function**, if**C3>50**returns**TRUE**, then the**IF function**returns**“Passed”**. Otherwise, the**IF function**will return**“Failed”**.

See the formula evaluation for Don

```
=IF(C3>50,"Passed","Failed")
=IF(TRUE,"Passed","Failed")
=Passed
```

**Example #2: Application of IF with Multiple Conditions**

The first example’s data set is modified to include the obtained score in two new subjects: math and geography.

You want to know if the students scored higher than **50** in all three subjects.

We will use the **AND function **to combine three conditions for the three subjects.

The structured formula will be as below:

`=IF(AND(C3>50,D3>50,E3>50),"Good","Not Good")`

- Input the above formula in cell
**F3**and press**Enter**. - Then Just
**AutoFill**

**Explanation: **

- The
**AND**function will return**TRUE**if all three conditions inside the**AND**function are satisfied. - If the
**AND**function returns**TRUE**, then the**IF function**will return**“Good”**. Otherwise, the**IF function**will return**“Not Good”.**

Let’s see the formula evaluation for **Don**

```
=IF(AND(C3>50,D3>50,E3>50),"Good","Not Good")
=IF(AND(60>50,45>50,88>50),"Good","Not Good")
=IF(AND(TRUE,FALSE,TRUE),"Good","Not Good")
=IF(FALSE,"Good","Not Good")
=Not Good
```

**Example #3: Application of Nested IF Function **

With reference to the previous example, you want to show results according to the following criteria:

The formula will be

`=IF(C3>50,IF(D3>50,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")`

**Explanation: **

- If condition
**C3>50**is satisfied, then the**IF function**will enter the nested**second IF function**and trigger the**second IF function**. Otherwise, the**first****IF function**jumps to returning**‘’Failed in Exam1″** - Then, if condition
**D3>50**is satisfied, the**IF function**will trigger the**third IF function**. Otherwise, the**second IF function**jumps to returning**“Failed in Exam 2**.” - Similarly, if condition
**E3>50**is satisfied, the**third IF function**returns**“Passed in all Exams.”**Otherwise, the third**IF function**jumps to returning**“Failed in all Exams”**

See the formula evaluation for **Don**

```
=IF(C3>50,IF(D3>50,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")
=IF(60>50,IF(D3>50,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")
=IF(TRUE,IF(D3>50,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")
=IF(TRUE,IF(45>50,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")
=IF(TRUE,IF(FALSE,IF(E3>50,"Passed in all Exam","Failed in all Exam"),"Failed in Exam2"),"Failed in Exam1")
=IF(TRUE,"Failed in Exam2","Failed in Exam1")
=Failed in Exam2
```

**Example #4: Verify Array Conditions by IF Function**

The** IF function **can be used to verify the array conditions.

In this example, we want to determine the average score obtained across three exams.

But if any student gets a **zero** on any exam, then the corresponding cell should be avoided.

Let’s apply the following formula to solve this problem

`=AVERAGE(IF(C3:E3<>0,C3:E3))`

- Apply the above formula in the cell
- After pressing
**Enter**,**AutoFill**the remaining cells in the column

**Explanation: **

The **IF function** will check the values in the array that are not equal to **0**. Then it returns the values that are not equal to **zero**.

Finally, the **AVERAGE** function calculates the average of the numbers returned by the** IF function.**

See the formula evaluation for **John**

```
=AVERAGE(IF(C4:E4<>0,C4:E4))
=AVERAGE(IF({TRUE,FALSE,TRUE},C4:E4))
=AVERAGE({80,FALSE,99})
=89.5
```

**Example #5: Avoiding Errors by IF Function in Excel**

When any number is divided by **zero**, Excel displays the **#DIV/0!** error.

In this example, we will avoid the **#DIV/0! **error by using the IF function with the help of the **ISERROR** function.

In the data set below, some students obtained **zero **in some subjects.

Let’s divide the score obtained in **Exam1** by **Exam 2** using the following formula

`=IF(ISERROR(C3/D3),"Can't divide by Zero",C3/D3)`

**Explanation:**

- The
**ISERROR**function catches the**errors**in Excel. The**ISERROR**function returns**TRUE**when any error is displayed in the output cell. - When the first logic,
**ISERROR(C3/D3)**, is**TRUE**, the**IF function**returns “**Can’t divide by zero**” in the output cell, according to the syntax of the**IF function**.

Let’s see the formula evaluation for **John**

```
=IF(ISERROR(C4/D4),"Can't divide by Zero",C4/D4)
=IF(TRUE,"Can't divide by Zero",C4/D4)
= Can't divide by Zero
```

You can rewrite the formula in the simplest version below

`=IF(D3=0,"Can't divide by Zero",C3/D3)`

You will get the same result, but in terms of processing speed, the **ISERROR** function is faster and more commonly used by professionals.

**Example #6: Finding Blank Cell by IF Function in Excel**

In the data given below, if any student is absent from the exam then the cell is kept blank

You want to show a user-friendly message that corresponds to whether the student was present or absent.

Apply the following formula to achieve the desired output:

`=IF(ISBLANK(C3),"Not Present","Present")`

**Explanation:**

- The
**ISBLANK**function returns**TRUE**if the cell given in the argument is really**empty**. - Then the
**IF function**returns “**Not Present**” when the**ISBLANK**function returns**TRUE**; otherwise, the**IF function**returns “Present”.

See the formula evaluation for **Smith**

```
=IF(ISBLANK(C5),"Not Present","Present")
=IF(TRUE,"Not Present","Present")
=Not Present
```

**Example #7: Application of IF function to Verify Multiple Boolean Logic**

In the data table provided below, the employee name, join date, and salary are provided.

You want to give a **10%** bonus to the employees whose joining year is **2019** and whose salary is less than **$500**.

We’ll use the **YEAR** function to see if the joining year is **2019** or not.

- Apply the following formula in the output cell
**E3**

`=IF((YEAR(C3)=2019)+(D3<500)=2,"10%","Not eligible")`

- Press the
**Enter**key on your keyboard - Then
**AutoFill**the remaining cell on the column**E**

**Explanation:**

- We know that the equivalent value of the Boolean return
**TRUE**or**FALSE**is**1**or**0**, respectively. **YEAR(C3)=2019**returns**TRUE**or**1**when the year match with**2019**- Similarly, when condition
**D3<500**is satisfied, it returns**TRUE**or**1**. **(YEAR(C3)=2019)+(D3<500)=2**returns**TRUE**if both conditions are**TRUE**. In other words,**TRUE**is returned when**1+1 = 2**, which satisfies the condition**(YEAR(C3)=2019)+(D3<500)=2**.- Then the
**IF function**returns**“10%”**if the first statement,**(YEAR(C3)=2019)+(D3<500)=2**returns**TRUE**. Otherwise, the**IF function**returns**“Not eligible”**

Let’s see the formula evaluation for **Shelly**

```
=IF((YEAR(C7)=2019)+(D7<500)=2,"10%","Not eligible")
=IF((TRUE,"10%","Not eligible")
=10%
```

**Example #8: IF Function with VLOOKUP Function**

The **IF function** is widely used with the lookup functions in Excel. Suppose you want to give a bonus to the employees whose salaries are less than **$500**.

In this example, we will solve this problem by applying the **IF function** along with the **VLOOKUP** function.

- Input the following formula in the output cell
**F4**

`=IF(VLOOKUP(E4,B3:C9,2,FALSE)<500,"Yes","No")`

- Hit the
**Enter**key

**Explanation:**

**VLOOKUP**function will return the salary from the range**B3:C9**corresponding to the entered value in cell**E4**- Then
**VLOOKUP(E4,B3:C9,2,FALSE)<500**will return**TRUE**if the value returned by the formula**VLOOKUP(E4,B3:C9,2,FALSE)**is less than**500**. - If
**VLOOKUP(E4,B3:C9,2,FALSE)<500**returns**TRUE**, then the**IF function**will return “**Yes**”. otherwise, the**IF function**will return**“No”**

Let’s examine the formula explanation for **Don**,

```
=IF(VLOOKUP(E4,B3:C9,2,FALSE)<500,"Yes","No")
=IF(700<500,"Yes","No")
=IF(FALSE,"Yes","No")
=No
```

**Example #9: IF Function with TEXT Function**

In this example, we will use the** IF function** to match the text value. For partial matching, we will use the** ISNUMBER** and **SEARCH** functions.

Suppose inventory data for items is given below.

You want to know the product availability for specific** types** and **colors**.

To solve this problem apply the following formula in the cell **C3**

`=IF(AND(ISNUMBER(SEARCH(F$3,B3)),ISNUMBER(SEARCH(F$4,B3))),"Available ","Not available")`

And then press the **Enter **button to **AutoFill **the residual cell in the column **C**.

**Explanation:**

**ISNUMBER(SEARCH(F$3,B3))**matches the input value in the cell**F3**with the texts in cell**B3**- Similarly
**ISNUMBER(SEARCH(F$4,B3))**matches the input value in the cell**F4**with the texts in cell**B3** **AND(ISNUMBER(SEARCH(F$3,B3)),ISNUMBER(SEARCH(F$4,B3))**returns**TRUE**only if the values in cell**F3**and**F4**found in the cell**B3**- If the
**AND**function returns**TRUE**, then the**IF function**returns**“Available**“. Otherwise, the**IF function**returns**“Not available**“

See the formula evaluation below:

```
=IF(AND(ISNUMBER(SEARCH(F$3,B3)),ISNUMBER(SEARCH(F$4,B3))),"Available ","Not available")
=IF(AND(TRUE,FALSE),"Available ","Not available")
=IF(FALSE,"Available ","Not available")
=Not available
```

## Things to Keep in Mind About the IF Function in Excel:

- The
**IF function**does not care about the case. But the application of the**EXACT**function enables the IF function to be**case-sensitive**. **Wildcards**are not supported by the**IF function**. If applied with the**SEARCH function**, then the**IF function**can perform similarly.- The
**IF function**can be used to evaluate**array**conditions. - When the given logical argument could be executed to return
**TRUE**or**FALSE**, the**#VALUE!**error occurs.

**Conclusion**

We hope you have been elevated to an advanced level in applying the **IF function** in Excel. If this article, ’’ **Beginner to Advanced IF Function in Excel** ‘’ is really helpful, please provide feedback. Please comment below.