IF Excel Function

What is IF Function in Excel?

IF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria.

For example, the IF formula in Excel can be applied as follows:

 “=IF(condition A,“value B”,“value C”)”

 The IF excel function returns “value B” if condition A is met and returns “value C” if condition A is not met.

It is often used to make logical interpretations which help in decision-making.

Syntax of the IF Excel Function

The syntax of the IF function is shown in the following image:

Excel IF Formula

The IF excel function accepts the following arguments:

  • Logical_test: It refers to the condition to be evaluated. The condition can be a value or a logical expression.
  • Value_if_true: It is the value returned as a result when the condition is “true”.
  • Value_if_false: It is the value returned as a result when the condition is “false”.

In the formula, the “logical_test” is a required argument, whereas the “value_if_true” and “value_if_false” are optional arguments.

The IF formula uses logical operators to evaluate the values in a range of cells. The following table shows the different logical operatorsLogical OperatorsIn Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more and their meaning.

OperatorMeaning
=Equal to
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to
<>Not equal to

How to Use IF Function in Excel?

Let us understand the working of the IF function with the help of the following examples in Excel.

You can download this IF Function Excel Template here – IF Function Excel Template

Example #1

If there is no oxygen on a planet, life is impossible. If oxygen is available on a planet, then life is possible. The following table shows a list of planets in column A and the information on the availability of oxygen in column B. We have to find the planets where life is possible, based on the condition of oxygen availability.

IF Function Example 1

Let us apply the IF formula to cell C2 to find out whether life is possible on the planets listed in the table.

The IF formula is stated as follows:

“=IF(B2=“Yes”, “Life is Possible”, “Life is Not Possible”)

The succeeding image shows the IF formula applied to cell C2.

IF Function Example 1-2

The subsequent image shows how the IF formula is applied to the range of cells C2:C5.

IF Function Example 1-4

Drag the cells to view the output of all the planets.

IF Function Example 1-3

The output in the below worksheet shows life is possible on the planet Earth.

Flow Chart of Generic IF Excel Function

Flow Chart of IF Function

The IF Function Flow Chart for Mars (Example #1)

Flow Chart of IF Function 1

The flow of IF function flowchart for Jupiter and Venus is the same as the IF function flowchart for Mars (Example #1).

The IF Function Flow Chart for Earth

Flow Chart of IF Function 2

Hence, the IF excel function allows making logical comparisons between values. The modus operandi of the IF function is stated as: If something is true, then do something; otherwise, do something else.

Example #2

The following table shows a list of years. We want to find out if the given year is a leap year or not.

A leap year has 366 days; the extra day is the 29th of February. The criteria for a leap year are stated as follows:

  1. The year will be exactly divisible by 4 and not exactly be divisible by 100 or
  2. The year will be exactly divisible by 400.
IF Function Example 2

In this example, we will use the IF function along with the AND, OR, and MOD functions to find the leap years.

We use the MOD function to find a remainder after a dividend is divided by a divisor.

The AND functionAND FunctionThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE.read more evaluates both the conditions of the leap years for the value “true”. The OR function evaluates either of the condition for the value “true”.

We will apply the MOD function to the conditions as follows:

If MOD(year,4)=0 and MOD(year,100)<>(is not equal to) 0, then the year is a leap year.

or

If MOD(year,400)=0, then the year is a leap year; otherwise, the year is not a leap year.

The IF formula is stated as follows:

“=IF(OR(AND((MOD(year,4)=0),(MOD(year,100)<>0)),(MOD(year,400)=0)),“Leap Year”, “Not A Leap Year”)”

The argument “year” refers to a reference value.

The following images show the output of the IF formula applied in the range of cells.

IF Function Example 2-1
IF Function Example 2-2

The following image shows how the IF formula is applied to the range of cells B2:B18.

IF Function Example 2-4

The succeeding table shows the years 1960, 2028, and 2148 as leap years and the remaining as non-leap years.

IF Function Example 2-3

The result of the IF excel formula is displayed for the range of cells B2:B18 in the following image.

IF Function Example 2-5

Example #3

The succeeding table shows a list of drivers and the directions they undertook to reach the destination.  It is preceded by an image of the road intersection explaining the turns taken by the drivers and their destinations. The right turn leads to town B, and the left turn leads to town C. Identify the driver’s destination to town B and town C.

Road Intersection Image

IF Function (Destination Example)
IF Function Example 3-1

Let us apply the IF excel function to find the destination. Here, the condition is mentioned as follows:

  • If the driver turns right, he/she reaches town B.
  • If the driver turns left, he/she reaches town C.

We use the following IF formula to find the destination:

 “=IF(B2=“Left”, “Town C”, “Town B”)”

The succeeding image shows the output of the IF formula applied to cell C2.

IF Function Example 3-2

Drag the cells to use the formula in the range C2:C11. Finally, we get the destinations of each driver for their turning movements.

The below image displays the IF formula applied to the range.

IF Function Example 3-3

The output of the IF formula and the destinations are displayed in the succeeding image.

IF Function Example 3-4

The result shows that six drivers reached town C, and the remaining four have reached town B.

Example #4

The following table shows a list of items and their inventory levels. We want to check if the specific item is available in the inventory or not using the IF function.

Example 4

Let us list the name of items in column A and the number of items in column B. The list of data to be validated for the entire items list is shown in the cell E2 of the below image.

Example 4-1

We use the Excel IF along with the VLOOKUP functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address are unique identifiers.read more to check the availability of the items in the inventory.

The VLOOKUP function looks up the values referring to the number of items, and the IF function will check whether the item number is greater than zero or not.

We will apply the following IF formula in the F2 cell:

“=IF(VLOOKUP(E2,A2:B11,2,0)=0, “Item Not Available”,“Item Available”)”

If the lookup value of an item is equal to 0, then the item is not available; else, the item is available.

The succeeding image shows the result of the IF formula in the cell F2.

Example 4-2

Select “bat” in the E2 item cell to know whether the item is available or not in the inventory (as shown in the following image).

Example 4-3

Example #5

The following table shows the list of students and their marks. The grade criteria are provided based on the marks obtained by the students.  We want to find the grade of each student in the list.

Example 4-4

We apply the  Nested IF in Excel since we have multiple criteria to find and decide each student’s grade.

The Nesting of IF function uses the IF function inside another IF formula when multiple conditions are to be fulfilled.

The syntax of Nesting of IF function is stated as follows:

“=IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))”

The succeeding table represents the range of scores and the grades, respectively.

Example 4-5

Let us apply the multiple IF conditions with AND function in the below-nested formula to find out the grade of the students:

“=IF((B2>=95),“A”,IF(AND(B2>=85,B2<=94),“B”,IF(AND(B2>=75,B2<=84),“C”,IF(AND(B2>=61,B2<=74),“D”,“F”))))”

The IF function checks the logical condition as shown in the formula below:

“=IF(logical_test, [value_if_true],[value_if_false])”

We will split the above-mentioned nested formula and check the IF statements as shown below:

First Logical Test: B2>=95

If the formula returns,

  • Value_if_true, execute: “A” (Grade A) else(comma) enter value_if_false
  • Value_if_false, then the formula finds another IF condition and enter IF condition

Second Logical Test: B2>=85(logical expression 1) and B2<=94(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

  • Value_if_true,  execute: “B” (Grade B) else(comma) enter value_if_false
  • Value_if_false, then the formula finds another IF condition and enter IF condition

Third Logical Test: B2>=75(logical expression 1) and B2<=84(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

  • Value_if_true,  execute: “C” (Grade C) else(comma) enter value_if_false
  • value_if_false, then the formula finds another IF condition and enter IF condition

Fourth Logical Test: B2>=61(logical expression 1) and B2<=74(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

  • Value_if_true, execute: “D” (Grade D) else(comma) enter value_if_false
  • Value_if_false, execute: “F” (Grade F)
  • Finally, close the parenthesis.

 The below image displays the output of the IF formula applied to the range.

Example 4-6

The succeeding image shows the IF nested formula applied to the range.

Example 4-7

The grades of the students are listed in the following table.

Example 4-8

Guidelines for the Multiple IF Statements

The guidelines for the multiple IF statements are listed as follows:

Frequently Asked Question

1. What is the IF function in Excel?

The Excel IF function is a logical function that checks the given criteria and returns one value for a “true” and another value for a “false” result.

The syntax of the IF function is stated as follows:
“=IF(logical_test, [value_if_true], [value_if_false])”

The arguments are as follows:
1. Logical_test – It refers to a value or condition that is tested.
2. Value_if_true – It is the value returned when the condition logical_test is “true.”
3. Value_if_false – It is the value returned when the condition logical_test is “false.”

The “logical_test” is a required argument, whereas the “value_if_true” and “value_if_false” are optional arguments.

2. How to use the IF Excel function with multiple conditions?

The IF Excel statement for multiple conditions is created by using multiple IF functions in a single formula.

The syntax of IF function with multiple conditions is stated as follows:

“=IF (condition 1_“true”, do something, IF (condition 2_“true”, do something, IF (condition 3_ “true”, do something, else do something)))”

3. How to use the function IFERROR in Excel?

The IFERROR functionIFERROR FunctionWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more in Excel is used to find and manage errors in formulas and calculations.

On checking a formula, if it finds an error, it returns another value specified by the user. Else it returns the result of the formula.

The syntax is stated as follows:
“=IFERROR(value, value_if_error)”
Where,
• Value – It refers to the value to be tested.
• Value_if_error – It refers to the value to be returned if the formula returns an error.
Both the arguments are the required arguments.

IF Excel Function Video

 

Recommended Articles

This has been a guide to the IF function in Excel. Here we discuss how to use the IF function along with examples and downloadable templates. You may also look at these useful functions –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *