Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel IF function

The IF function is one of simplest and most useful functions in Excel workbook. It performs a simple logical test which depending on the comparison result, and it returns one value if a result is TRUE, or another value if result is FALSE.


 Syntax:

The syntax for the IF function in Excel is:

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

doc if function 2


 Arguments:

  • logical_test: Required. It is the condition that you want to test.
  • value_if_true: Optional. A specific value that you want returned if the logical_test result is TRUE.
  • value_if_false: Optional. A value that you want returned if the logical_test result is FALSE.

Notes:

1. If value_if_true is omitted:

  • If the value_if_true argument is omitted in the IF function, such as only comma following the logical_test, it will return zero when the condition as met. For example: =IF(C2>100,, "Low ").
  • If you want to use a blank cell instead of the zero if the condition is met, you should enter double quotes "" into the second parameter, like this: =IF(C2>100, "", "Low").
doc if function 3 doc if function 4

2. If value_if_false is omitted:

  • If the value_if_false parameter is omitted in the IF function, it will return a FALSE when the specified condition is not met. Such as: =IF(C2>100, "High").
  • If you put a comma after the value_if_true argument, it will return a zero when the specified condition is not met. Such as: =IF(C2>100, "High" ,).
  • If you enter double quotes "" into the third parameter, an empty cell will return if the condition is not met. Such as: =IF(C2>100, "High" , "").
doc if function 5 doc if function 6 doc if function 7

 Return:

Test for a specific condition, returns the corresponding value that you supply for TRUE or FALSE.


 Examples:

Example 1: Using a simple IF function for numbers

For example, supposing, you want to test a list of values, if the value is greater than a specific value 100, a text “Good” is displayed, if not, a text “Bad” is returned.

Enter the below formula, and you will get the below result as you need.

=IF(B2>100,"Good","Bad")

doc if function 8


Example 2: Using IF function for text values

Case 1: IF function for text values with case insensitive:

Here, I have a table with a list of Tasks and Completion Status, now, I want to know which tasks need to be go on, and which needn’t. When the text in Column C is completed, “No” will be displayed, otherwise, “Yes” will be returned.

Please apply the following formula, now, the cell will return “No” when text in column C is displayed as “completed”, no matter it is uppercase or lowercase; if other text in column C, “Yes” will be returned. See screenshot:

=IF(C2="completed", "No", "Yes")

doc if function 9

Case 2: IF function for text values with case sensitive:

To test the text values with case sensitive, you should combine the IF function with the EXACT function, please apply below formula, then only the text with the exact match will be recognized, and you will get the below result as you want:

=IF(EXACT(C2,"COMPLETED"), "No", "Yes")

doc if function 10

Case 3: IF function for text values with partial match:

Sometimes, you need to check the cell values based on partial text, in this case, you should use the IF, ISNUMBER and SEARCH functions together.

For example, if you want to check the cells which contain the text “comp”, and then return the corresponding values, please apply the below formula. And you will get the result as below screenshot shown:

=IF(ISNUMBER(SEARCH("comp",C2)), "No", "Yes")

doc if function 11

Notes:

  • 1. The SEARCH function is applied for text with case insensitive, if you want to check the text with case sensitive, you should replace the SEARCH function with FIND function. Like this:=IF(ISNUMBER(FIND("comp",C2)), "No", "Yes")
  • 2. The text values as parameters in the IF formulas, you must enclose them in "double quotes".

Example 3: Using IF function for date values

Case 1: IF function for dates to compare dates with a specific date:

If you want to compare dates to check if they are greater or less than a specific date, the IF function also can do you a favor. As the IF function cannot recognize a date format, you should combine a DATEVALUE function with it.

Please apply this formula, when the date is greater than 4/15/2019, a “Yes” will be returned, otherwise, the formula will return a “No” text, see screenshot:

=IF(D4>DATEVALUE("4/15/2019"), "Yes", "No")

doc if function 12

Note: In the above formula, you can use the cell reference directly without using the DATEVALUE function as well. Like this: =IF(D4>$D$1, "Yes", "No").

doc if function 13

Case 2: IF function for dates to check dates is greater or less than 30 days:

If you want to identify the dates which are greater or less than 30 days from current date, you can combine the TODAY function with the IF function.

Please enter this formula:

Identify the date older than 30 days: =IF(TODAY()-C4>30,"Older date","")

Identify the date greater than 30 days: =IF(C4-TODAY()>30, "Future date", "")

doc if function 14 doc if function 15

Note: If you would like to put the both results into one column, you need use a nested IF function as this:

=IF(C4-TODAY()>30, "Future date", IF(TODAY()-C4>30, "Older date", ""))

doc if function 16


Example 4: Using IF function with AND, OR function together

It is a common usage for us to combine the IF, AND, OR functions together in Excel.

Case 1: Using the IF function with AND functions to check if all conditions are true:

I want to check if all the conditions I set are met, such as: B4 is Red, C4 is Small and D4>200. If all conditions are TURE, mark the result as “Yes”; If either condition is FALSE, then return “No”.

Please apply this formula, and you will get the result as following screenshot shown:

=IF(AND(B4="Red",C4="Small", D4>200),"Yes","No")

doc if function 17

Case 2: Using the IF function with OR functions to check any one of the conditions is true:

You can also use IF and OR functions to check if any one of the conditions is true, for example, I want to identify if the cell in column B contains the “Blue” or “Red” text, if any text in column B, Yes is displayed, otherwise, No is returned.

Here, you should apply this formula, and the below result will be shown:

=IF(OR(B4="Red",B4="Blue"),"Yes","No")

doc if function 18

Case 3: Using the IF function with AND and OR functions together:

This example, I will combine the IF function with both AND & OR functions at the same time. Supposing, you should check the following conditions:

  • Condition 1: Column B = “Red” and Column D > 300;
  • Condition 2: Column B = “Blue” and Column D > 300.

If either of the above conditions is met, a Match is returned, otherwise, No.

Please use this formula, and you will get the below result as you need:

=IF(AND(OR(B4="Red",B4= "Blue"), D4>300), "Match", "No")

doc if function 19


Example 5: Using Nested IF function

IF function is used to test a condition and return one value if the condition is met and another value if it is not met. But, sometimes, you should need to check more than one condition at the same time and return different values, you can use Nested IF to solve this job.

A Nested IF statement which combines multiple IF conditions, it means putting an IF statement inside another IF statement and repeating that process multiple times.

The syntax for Nested IF function in Excel is:

=IF (condition1, result1, IF (condition2, result2, IF (condition3, result3,…)))

Note: In Excel 2007 and later versions, you can nest up to 64 IF functions in one formula, and in Excel 2003 and earlier versions, only 7 nested IF functions can be used.

Case 1: Nested IF function to check multiple conditions:

A classic use of the Nested IF function is to assign letter grade for each student based on their scores. For example, you have a table with students and their exam scores, now you want to classify the scores with the following conditions:

doc if function 20

Please apply this formula, and you will get the below result, if the score is greater or equal to 90, the grade is “Excellent”, if the score is greater or equal to 80, the grade is “Good”, if the score is greater or equal to 60, the grade is “Medium”, otherwise, the grade is “Poor”.

=IF(C2>=90, "Excellent", IF(C2>=80, "Good", IF(C2>=60, "Medium", "Poor")))

doc if function 21

Explanation of the above formula:

doc if function 22
  • IF(check if C2>=90, if true - returns "Excellent", or else
  • IF(check if C2>=80, if true - returns "Good", or else
  • IF(check if C2>=60, if true - returns "Medium", if false - returns "Poor")))

Case 2: Nested IF function for calculating price based on quantity:

The Nested IF function also can be used to calculate the product price based on quantity.

For example, you want to provide customers a price break based on quantity, more quantity they purchase, more discount they will get as below screenshot shown.

doc if function 23

As the total price equals quantity multiply the price, so you should multiply the specified quantity by the value returned by nested Ifs. Please use this formula:

=D2*IF(D2>=101,16, IF(D2>=50, 21, IF(D2>=25, 26, IF( D2>=11, 30, IF(D2>=1, 39, "")))))

doc if function 24

Note: You can also use the cell references to replace the static price numbers, when the source data changing, you shouldn’t need to update the formula, please use this formula: =D2*IF(D2>=101, B6, IF(D2>=50, B5, IF(D2>=25, B4, IF( D2>=11, B3, IF(D2>=1, B2, "")))))

doc if function 25

Tips: Using the IF function to construct a test, you can use the following logical operators:

Operator Meaning Example Description
> Greater than =IF(A1>10, "OK",) If the number in cell A1 is greater than 10, the formula returns "OK"; otherwise 0 is returned.
< Less than =IF(A1<10, "OK", "") If the number in cell A1 is less than 10, the formula returns "OK"; otherwise an empty cell is returned.
>= Greater than or equal to =IF(A1>=10, "OK", "Bad") If the number in cell A1 is greater than or equal to 10, it will return "OK"; otherwise, "Bad" is displayed.
<= Less than or equal to =IF(A1<=10, "OK", "No") If the number in cell A1 is less than or equal to 10, it returns "OK"; otherwise, “No” is returned.
= Equal to =IF(A1=10, "OK", "No") If the number in cell A1 is equal to 10, it returns "OK"; otherwise it displays "No".
<> Not equal to =IF(A1<>10, "No", "OK") If the number in cell A1 is not equal to 10, the formula returns "No "; otherwise - "OK".

Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.