How to Use IF function in Excel?

IF function in Excel is a versatile tool that can dramatically enhance your data analysis and decision-making processes. By applying conditional logic, you can automate data categorization, perform complex calculations, and tailor your spreadsheets to respond dynamically to varying scenarios. This function serves as the backbone for creating more interactive, accurate, and user-friendly Excel models. Embrace the full potential of the IF function in Excel to streamline your workflows, reduce manual errors, and unlock new levels of insight into your data.

This Tutorial Covers:

What is IF function in Excel
Basic IF formula example in Excel
Excel If then formula: things to know
- If value_if_true is omitted
- If value_if_false is omitted
Using IF function in Excel – formula examples
- Excel IF function with numbers
- Excel IF function with text
- Case-sensitive IF statement for text values
- If cell contains partial text
- Excel IF statement with dates
Excel IF statement for blanks and non-blanks
Multiple IF Formula in Excel
- Nested IF formula
- AND/OR Criteria
- IF-AND Function
- IF-OR Function
IF error in Excel
Is IF Function the Best Solution

1. What is IF function in Excel?

When a given condition is evaluated, one of the logical functions called IF returns one value if the condition is TRUE and another value if it is FALSE.

The syntax of the IF function is shown below:

IF(logical test, [value if true], [value if false])

As you can see, IF requires three arguments in total, but only the first one is required; the other two are optional.

The test condition is logical test, which is essential. Can be considered as being either TRUE or FALSE.

Value if true (optional) – the value to return when the logical test evaluates to TRUE, i.e., the condition is met. If omitted, the value if false option must be defined.

Value if false (optional) – the value to return when the logical test evaluates to FALSE, i.e., the condition is not met. If omitted, the value if true parameter must be set.

2. Basic IF formula example in Excel

To make a simple IF formula in Excel, this is what you need to do:

For a logical test, write an expression that returns either TRUE or FALSE. For this, you’d generally use one of the logical operators.

For value if true, describe what to return when the logical test evaluates to TRUE.

For value, if false, describe what to return when the logical test evaluates to FALSE. Though this argument is optional, we recommend always specifying it to avoid unexpected consequences.

As such an example, let’s build a very simple IF formula that verifies a value in cell A2 and outputs “Good” if the values are greater than 80, “Bad” or else:

=IF(B2>80, “Good”, “Bad”)

This equation goes to C2, and then is repeated down until C7:

In a situation, you wish to return a value once the criteria is fulfilled (or not fulfilled), elsewhere – nothing, using an empty string (“”) for the “null” argument. For example:

=IF(B2>80, “Good”, “”)

This equation will output “Good” if the value in A2 is much more than 80, an empty cell or else:

3. Excel If then formula: things to know

Even though the IF function’s final two parameters are optional, if you don’t understand the underlying reasoning, your formula might yield unexpected results.

If value_if_true is omitted:

If the 2nd argument of your Excel IF formula is removed (i.e. there are two consecutive commas after the logical test), you’ll obtain zero (0) when the condition is met, which makes no sense in most circumstances. Here is an example of such a formula:

=IF(B2>80, , “Bad”)

To return a blank cell instead, offer an empty string (“”) for the second parameter, like this:

=IF(B2>80, “”, “Bad”)

The difference is shown in the screenshot below:

If value_if_false is omitted:

The following outcomes will be produced if the third parameter of IF is omitted and the logical test evaluates to FALSE.

Value if true will yield the logical value FALSE if the remainder of the statement is only a closing bracket. Quite unexpected, isn’t it? Here is an example of such a formula:

=IF(B2>80, “Good”)

Excel will forcefully return 0, which also doesn’t make much sense, if you type a comma after the value if true argument:

=IF(B2>80, “Good”,)

The best strategy is to get a blank cell when the condition is not true by using a zero-length string (“”):

=IF(B2>80, “Good”, “”)

Tip: If the condition is met or not, offer TRUE for value if true and FALSE for value if false to return a logical value. For the results to be Boolean values that other Excel functions can recognize, don’t wrap TRUE and FALSE in double quotes since this will change them into normal text values.

4. Using IF function in Excel – formula examples

Now that you are familiar with the syntax of the IF function, let’s look at some formula examples and discover when and when to employ the If then sentences.

Excel IF function with numbers:

To write an IF statement for integers, utilize logical operators such as:

Equal to (=)
Not the same as (<>)
More than (>)
greater than (>) or greater than equivalent to (>=)
Lower than (<)
Less than equal to (<=)

Above, you have already seen an example of such a formula that tests if a number is bigger than a particular number.

And here’s a formula that checks if a cell has a negative number:

=IF(B2<0, “Invalid”, “”)

For negative values (which are less than 0), the algorithm returns “Invalid”; for zeros and positive numbers – a blank cell.

Excel IF function with text:

Typically, you use either the “equal to” or “not equal to” operator when writing an IF statement for text values.

For instance, the formula below examines the Delivery Status in B2 to see if an action is necessary or not:

=IF(B2=”delivered”, “No”, “Yes”)

The formula reads as follows: if B2 equals “delivered,” return “No,” else, return “Yes.”

Using the “not equal to” operator and switching the value if true and the value if false values is another technique to get the same result:

=IF(B2<>”delivered”, “Yes”, “No”)

Notes:

Remember to always wrap text values for IF’s parameters in double quotations.

The IF function in Excel is often case-insensitive. It does not distinguish between “delivered,” “Delivered,” and “DELIVERED” in the case above.

Case-sensitive IF statement for text values:

Use IF together with the case-sensitive EXACT function to treat capital and lowercase letters differently.

For instance, you might use the following formula to only return “No” if B2 contains the capital word “DELIVERED”:

=IF(EXACT(B2,”DELIVERED”), “No”, “Yes”)

If cell contains partial text:

Using wildcards in the logical test immediately comes to mind as a solution when you wish to base the condition on partial match rather than exact match. But this straightforward strategy won’t work. IF sadly does not support wildcards, despite the fact that many functions do.

Use of IF in conjunction with ISNUMBER, SEARCH (case-insensitive), or FIND is a viable option (case-sensitive).

The following formula will work perfectly, for instance, if “No” action is needed for both “Delivered” and “Out for delivery” items:

=IF(ISNUMBER(SEARCH(“deliv”, B2)), “No”, “Yes”)

Excel IF statement with dates:

IF formulations for dates may initially appear to be similar to IF statements for numeric and text variables. Sadly, that is not the case. Unlike many other functions, IF does recognize dates in logical tests and interprets them as plain text strings. In other words, you cannot enter a date in the form of “12/11/2021” or “>12/11/2021”. It is necessary to enclose a date in the DATEVALUE function in order for the IF function to recognize it.

Here’s an example of how to determine whether a given date is greater than another one:

=IF(B2>DATEVALUE(“09/12/2022”), “Coming soon”, “Released”)

This formula checks the dates in column B and returns “Coming soon” if a movie is slated for 09-Dec-2022 or later, “Released” for a preceding date.

Naturally, nothing would stop you from entering the target date in a predetermined cell (let’s say E2) and making a reference to that cell. Just be sure to use the dollar symbol to make the cell address an absolute reference by locking it. For instance:

=IF(B2>$E$2, “Coming soon”, “Released”)

Use the TODAY() function to compare a date to the current date. For instance:

5. Excel IF statement for blanks and non-blanks

If you want to indicate your data in some way depending on whether or not a certain cell or cells are empty, you can either:

Use ISBLANK and the IF function together, or
Use the logical operators >” or =”” (equivalent to blank) (not equal to blank).

With formula examples, the table below outlines the distinctions between these two methods.

Logical test

Description

Formula Example

=””

regardless of whether it contains a string of zero length, evaluates to TRUE if a cell appears to be empty.

Evaluates to FALSE if not.

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

Returns 0 if A1 is visually blank. If not, 1 is returned.

The formula yields 0 if A1 includes an empty string (“”).

Blank cells

ISBLANK()

If a cell has no formula, blank spaces, or empty strings, it evaluates to TRUE.

Evaluates to FALSE if not.

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

Returns 1 in the absence of any data in A1, and 0 otherwise.

The formula gives 1 if the empty string (“”) is present in A1.

Non-blank cells

<>””

if a cell contains data, evaluates to TRUE. evaluates to FALSE if not.

Blank cells are those that have strings with zero lengths.

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

Returns 1 when A1 is not blank and 0 when it is.

The formula returns 0 if the empty string in A1 is present.

ISBLANK()=FALSE

if a cell is not empty, evaluates to TRUE. evaluates to FALSE if not.

Cells with strings of zero length are regarded as non-blank.

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

Similar to the formula above, but instead returns 1 if A1 is empty.

Let’s explore examples of both blank and non-blank IF statements now. Assume that a date only appears in column B if a movie has already been released. Use one of the following formulas to identify the released movie:

=IF(B2=””, “”, “Released”)

=IF(ISBLANK(B2), “”, “Released”)

=IF($B2<>””, “Released”, “”)

=IF(ISBLANK($B2)=FALSE, “Released”, “”)

If none of the cells under test contain zero-length strings, all formulas will produce the same results:

6. Multiple IF Formula in Excel

In Excel, there are essentially two approaches to writing numerous IF statements:

multiple IF functions being nested inside of one another
using the logical test’s AND or OR function
Nested IF formula:

Multiple IF statements can be placed in the same cell using nested IF functions, allowing you to test many conditions within a single formula and return various values based on the outcomes of those tests.

As an example, consider the nested IF formula in cell C2 below.

=IF(B2<33, “F”,IF(B2<70, “B”,IF(B2<80, “A”,IF(B2>80, “A+”))))

Explanation: If the score is less than 33, the nested IF formula produces F; if it is lower than 70, it returns B; if it is lower than 80, it returns A; and if it is more than 80, it returns A+.

IF-AND Function:

Consequently, you must combine the IF and AND functions in one formula to construct the IF AND statement. And that is how:

=IF(AND(condition1, condition2,…), value_if_true, value_if_false)

Take a look at the IF-AND function in cell D2 below as an explanation.

=IF(AND(B2>=40, C2>=60), “Pass”, “Fail”)

Explanation: If the first score is more than or equal to 40 and the second score is greater than or equal to 60, the AND function returns TRUE; otherwise, it returns FALSE. The IF function returns Pass in the TRUE case and Fail in the FALSE case.

IF-OR Function:

Put the OR function within the logical test of IF to evaluate two or more conditions and return two results: one if any of the conditions is TRUE, and another if all the conditions are FALSE.

=IF(OR(condition1, condition2,…), value_if_true, value_if_false)

Take a look at the IF-OR function in cell D2 below as an explanation.

=IF(OR(B2>=40, C2>=40), “Pass”, “Fail”)

Explanation: If at least one score is greater than or equal to 40, the OR function returns TRUE; otherwise, it returns FALSE. The IF function returns Pass in the TRUE case and Fail in the FALSE case.

AND/OR Criteria:

Learn how to use the IF function in conjunction with the AND and OR functions to master Excel.

Take a look at the IF function or AND/OR criteria in cell D2 below as an explanation.

=IF(AND(A2=”Pencil”,OR(B2=”Green”,B2=”Blue”)),0.3*C2,0.2*C2)

Explanation: The AND function above has two arguments separated by a comma (Pencil, Green or Blue). The AND function returns TRUE if Product equals “Pencil” and Color equals “Green” or “Blue”. If TRUE, the IF function reduces the price by 30%, if FALSE, the IF function reduces the price by 20%.

7. IF error in Excel

To check for calculation problems, Excel 2007 added a unique function called IFERROR. The IFNA function is available in Excel 2013 and later to handle #N/A errors.

However, there may be some situations where utilizing the IF function in conjunction with ISERROR or ISNA is the preferable course of action. IF ISERROR is essentially the formula to use when you want to return one thing if there is an error and a different thing if there isn’t. This is not possible with the IFERROR function since it always provides the outcome of the primary formula if there is no error.

For instance, you might put the following formula in C2 and repeat it all the way down to C7 to compare each mark in column B against the top 3 marks in E2:E4 and return “Yes” if a match occurs, “No” otherwise:

=IF(ISERROR(MATCH(B2, $E$2:$E$4, 0)), “No”, “Yes” )

8. Is IF Function the Best Solution?

Question: Is the IF function or the IFS function always the best choice to employ when you need to determine whether one or more logical conditions are TRUE or FALSE, then show a result?

Answer: Not typically, no. Other Excel functions offer a more effective solution to the issue if you need to perform any more complex tests than a straightforward True or False test.

Application of IF function in Excel

Conditional Calculations: Use the IF function to perform different calculations based on specific criteria. For example, calculating bonuses for employees only if their sales exceed a certain threshold.
Data Categorization: Employ the IF function to categorize data automatically. For instance, categorizing test scores as “Pass” or “Fail” based on a predetermined passing score.
Budget Analysis: Apply the IF function to compare actual spending against budgeted amounts, flagging overages with alerts like “Over Budget” or showing “Within Budget” for compliance.
Attendance Tracking: Utilize the IF function to track attendance records, marking employees as “Present,” “Absent,” or “Late” based on their check-in times.
Sales Commission Calculation: Calculate commissions for sales personnel using the IF function to apply different commission rates based on achieved sales tiers.
Automated Status Updates: Implement the IF function to update project status automatically, such as changing the status to “Complete” only when all project tasks are marked as done.