The IF function in excel is one of the most used Excel functions and is used to test a condition. The condition tested returns one value if the condition is met and another value if the condition is met. For example, a company pays performance bonus to only those employees who meet a specific set target at the beginning of the year. The company has thousands of employees and it is not practical to manually check against each employee who has met the set target. IF function can be used to check this condition. If the sales target is more or equal to the set target, IF function will return a “TRUE” and if it does not meet the set target then it will return a “FALSE”.

We can use the IF statement with many other excel functions to solve many complex real life issues. We can also include several IF functions in one formula also known a nesting IF statement. Financial Analyst and professionals often use IF function to evaluate various situations that require a decision making. IF function is also used to evaluate values, texts and errors and we can use mathematic calculators to perform some additional calculations.

Syntax of the IF function in excel:

IF function has three arguments, the first argument is obligatory and the second two are optional.

• Logical test (mandatory): is any value or expression that can be evaluated to TRUE or FALSE.
• Value_if_true (optional): is the value that is returned if Logical test is TRUE. If omitted, TRUE is returned. You can nest up to seven IF functions.
• Value_if_false(optional): is the value that is returned if Logical test is FALSE. If omitted, FALSE is returned.

When we are using IF function in excel to test a condition, we normally use

• Logical operators like:
  • = (equal to)
  • > (greater than)
  • < (less than)
  • >= (greater than or equal to)
  • <= (less than or equal to)
  • <> (not equal to)
• For Value_if _true: specify what value or text to return when the logical test evaluates to TRUE.
• For Value_if_false: specify what value or text to return when the logical test evaluates to FALSE.

Example: We want to check if the sales exceed the target set by the company by any employee, return a value “Pay Bonus” or otherwise return a value “Not to pay Bonus”.

Syntax:

=IF(C3>=$H$2,”Pay Bonus”, “Not to pay Bonus”)

In order to drag the formula from D3 to D12, freeze the cell H2.

We can use an empty string (“”) for an undefined argument, if we want to return only one value for the condition being met or condition not being met. For example, in the above example we want to return value “Pay Bonus” only when the sales target is met and for sales target not being met we do not want any value in the column Bonus to pay or not.

Syntax: =IF(C3>=$H$2,”Pay Bonus”,””)

IF (Value_if_true) or (Value_if_false) is omitted

 If the second or the third argument of the IF function is omitted, it will return zero (0) as the value, which makes no sense. So if we want to return a blank cell, we have to input empty string (‘’) for the same.

Taking the above example, if we do not want any value in the Value_if_false argument and leave it blank, we get the following results in the following situations.

If we put a comma (,) after the Value_if_true argument and then a close bracket.

Syntax: =IF(C3>=$H$2,”Pay Bonus”,)

If we do not put a comma (,) after the Value_if_true argument and then close the bracket.

Syntax: =IF(C3>=$H$2,”Pay Bonus”)

If we put an empty string (“”) in the Value_if_false argument.

Syntax: =IF(C3>=$H$2,”Pay Bonus”,””)

So from the above three examples we can see that the most logical approach is to use an empty string (“”) to get a blank cell when the condition is not met. The same can be done for condition if met.

Using IF function in EXCEL in real life scenarios

1. IF function in Excel with numbers

We generally use logical operators as discussed above when we are building an IF statement for numbers. We can refer the above examples for the Excel IF function with numbers.

2. Excel IF function with text

We generally write an IF statement using either equal to (=) or not equal to (<>) operator for text values.

Text values need to be always enclosed in double quotes when used in IF function.

Example: A company maintains a stock of different raw materials in the store room and it wants to know whether to place a re-order for a particular raw material or not. If it has stock available, then return a value “No” and if the stock is not available then return a value “Yes” in the column Re-order required.

Syntax: =IF (C3=”In Stock”,”No”,”Yes”)

We can achieve the same results by using not equal to operator (<>) as follows:

Syntax: =IF (C3<>”In Stock”,”Yes”,”No”)

Point to be noted: By default, IF function is case-insensitive and does not differentiate between “In Stock”, “IN STOCK” or “in stock”.

3. If Statement With Case_Sensitive Text Values

We have to use IF function combined with the case-sensitive EXACT function to treat respect the Upper case text as Upper case and Lower case text as lower case. Only when the logical test matches the case-sensitive value, it will return a true value, otherwise it will return a false value.

Example: Taking the above example we want to return “No” only when Stock Status is “IN STOCK”, otherwise “Yes”.

Syntax: =IF(EXACT(C3,”IN STOCK”),”No”,”Yes”)

4. IF function used to check partial text

If in a given situation we want to base the condition so that on basis of partial match with a text, it returns a true or false value instead of exact match, one solution that may come to our minds is using the wildcard characters (like * or ?). However, sadly, IF function does not support wildcard characters. Instead one can use ISNUMBER and SEARCH function combined with IF function to get the desired results based on partial match.

Example: Now suppose taking the above example, The Company has re-ordered the raw materials that were out of stock. Now out of the materials re-ordered some have been delivered at the factory, while some are out for delivery and some still Out of Stock. The company wants to take an action only if the status is out of stock.

Syntax: =IF(ISNUMBER(SEARCH(“Deliv”,C3)),”No”,”Yes”)

5.IF Statement for Blank and Non Blank

Many a times we want to mark the data in a cell based on certain cells being empty or non- empty. We can do so by either using ISBLANK function along with IF function, or use the logical expressions like equal to blank (=””) or not equal to blank (<>””). For example, we want to return a value 0 if the cell is blank and 1 if it is not blank, we can use the following syntax:

SYNTAX

=IF(A1=””,0,1)

=IF(A1<>””,1,0)

=IF(ISBLANK(A1),0,1)

=IF(ISBLANK(A1)=FALSE,1,0)

Note: We will get the same results in all the four syntax

Example: The company has maintained a list of training seminars to be held in office. Against some seminars date is mentioned since it is completed while against some training programs no date is mentioned as it is still ongoing. We have to mark the status as “completed” against those programs which have date and mark the status as “ongoing” if there is no date.

6. IF Function with dates

IF function do not recognize the date in logical test. In order to make IF function recognize a date, we have to use DATEVALUE function along with IF function.

Example: A school has list of exams scheduled for students with exam dates. The teacher wants to check which exams are “completed” and which are “pending” as on date.

Syntax: =IF (C3>=DATEVALUE(“23/11/2023″),”Pending”,”Completed”)

Instead of using DATEVALUE function we can also use the following syntax and we will get the same results as above.

=IF(C3>=$G$2,”Pending”,”Completed”)

In order to test the results compared to current date we can use the syntax:

=IF(C3>=TODAY(),”Pending”,”Completed”)

7.IF Formula used to run another formula

IF function can also be used to execute another formula when a specific condition is met or not met. We can embed another mathematical expression or function in the 2^nd argument i.e. the Value_if_true and 3^rd argument i.e. Value_if_false.

Example: The company pays 5% variable allowance of Total Sales (in amount) to employees who achieved sales (in amount) less than $10,00,00 in a year and 10% variable allowance of Total Sales (in amount) to employees who achieved more than or equal to 10,00,00 in a year.

Syntax: =IF(C3>=$G$2,10%*C3,5%*C3)

8. IF function to check if two cells are same

In order to check if two cells in a given data set are equal we can use equal to (=) expression.

Example: A sports teacher wants to check in which game Team A and Team B have a tie.

Syntax: =IF(C3=D3,”Tie”,””)

However, to check if two cells are same in all aspects including the Lower and the Upper case, as in the case of passwords, we can use EXACT function along with the IF function.

Example: An IT personal is developing an APP and to login to the APP one has to one-time register giving the password and repeat password. If the two passwords match it will inbuilt a logic as passwords “matched”, otherwise “not matched”.

9. Nesting several IF statements

Nesting IF statements helps test multiple conditions within a single formula. Depending on the results of those tests, different values are returned.

Example: A company during its yearly appraisal assign grades to employees based on their performance throughout the year and based on those grades give them yearly bonus. Below is the grading system followed by the company.

Grade	Bonus
A-Outstanding	20%
B-Excellent	15%
C-Average	10%
D-Poor	5%

Syntax: =IF(C3=$F$3,$G$3,IF(C3=$F$4,$G$4,IF(C3=$F$5,$G$5,$G$6)))

10. IF statement with multiple conditions

We can test several conditions by nesting AND function or OR function in the logical test were:

• AND will return value “TRUE” only if all conditions are met and,
• OR will return value “TRUE” even if one condition is met.

              Example for AND function: A company pays bonus to employees if two of the conditions is met. First the employee has achieved sales of more than or equal to 10000, and second it has on-boarded 10 or more new clients during the year.

              Syntax: =IF(AND(C3>=10000,D3>=10),”Yes”,”No”)

               Example for OR function: A company pays bonus to employees if any of the two conditions is met. First the employee has achieved sales of more than or equal to 10000, and second it has on-boarded 10 or more new clients during the year.

              Syntax: =IF(OR(C3>=10000,D3>=10),”Yes”,”No”)

11. IF function for error

IF function can be used with ISERROR to check formulas for errors. It helps to return one value if there is an error and another value if there is no error, which is otherwise not possible with IFERROR function.

Example: Only If an employee gets Grade A and B are “eligible” for Promotion otherwise “not eligible”.

Nov 2024 Update:

A new variation of this is the IFS function in Excel introduced in Excel v2016 and above. It simplifies complex nested IF statements by allowing you to evaluate multiple conditions in a single formula rather than having to write an IF function everytime.

CONCLUSION

We have seen through the above examples that IF function can be used in many ways to solve various complex business problems and can be used as a powerful analysis tool by Finance professionals, analysts, schools, individuals on day to day basis. It is one of the best functions to explore since it supports testing multiple criteria at the same time and can be combined with other excel functions.