IF Function

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Functions  >  Logical  >  IF

Overview of the Excel IF Function

The Excel IF function tests a given condition and returns one value for a TRUE result and another value for a FALSE result. For example, we could determine if someone passed or failed a test where the passing grade is 50: =IF(A1<50, “Fail”, “Pass”).  The IF function can be used to evaluate a single condition or we can use multiple IF functions in the same formula, to test for multiple conditions.  Including multiple IF functions in a single formula is known as nesting IF functions. For more details, see Microsoft’s official IF function documentation.

Syntax

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

Arguments

logical_test – a logical expression that can be evaluated as TRUE or FALSE
value_if_true – [optional] the value if the logical test returns TRUE
value_if_false – [optional] the value if the logical test returns FALSE

Version

Excel 2003 and onward

Purpose

The Excel IF function runs a logical test on a given input and returns one value for a TRUE result and another value for a FALSE result.  The IF function is one of the most widely used functions in Excel, mainly due to it’s simplicity and versatility.  The first argument, the logical_test, is an expression that should be evaluated as being either TRUE or FALSE.  The second argument, the value_if_true, is what Excel will return if the logical test is evaluated to be TRUE.  The third argument, the value_if_false, is what Excel will return if the logical test is evaluated to be FALSE. 

For example, assuming the value in cell A1 is 40, we could use the IF function accordingly:

=IF(A1 < 50, “Fail”, “Pass”) // returns Fail

In the example above, the IF statement essentially says: if the value in cell A1 is less than 50, return “Fail”, otherwise, return “Pass”.  Since we know the value in cell A1 is 40, the formula returns “Fail”.  

Conversely, if the value in cell A1 is 70, the result would be as follows:

=IF(A1 < 50, “Fail”, “Pass”) // returns Pass

Now we see the same formula return “Pass”, because the value in cell A1 is not less than 50.

If the value_if_true and value_if_false arguments are left blank, Excel will default the result to be either TRUE or FALSE.  Again, assuming the value in cell A1 is 40, we could use the IF function accordingly:

=IF(A1 < 50) // returns TRUE

Conversely, if the value in cell A1 is 70, the result would be as follows:

=IF(A1 < 50) // returns FALSE

Before we get into some practical examples of the IF function, let’s first take a look at all of the different comparison logical operators that can be used in the logical_test.

Logical OperatorMeaningExample
=equal toA1 = B1
<>not equal toA1 <> B1
<less thanA1 < B1
<=less than or equal toA1 <= B1
>greater thanA1 > B1
>=greater than or equal toA1 >= B1

For all of the operators above, the examples read from left-to-right.  For example, the equal sign means that the value in cell A1 is equal to the value in cell B1.  Similarly, for the greater than sign, the value in cell A1 is greater than the value in cell B1.

The IF function does not support wildcards.

Examples of the Excel IF Function

Let’s look at a few examples of how the IF function works.

First, let’s explore an example where we are trying to determine if an individual qualifies for a bonus based on their sales for the month.  Assuming the individual needs to sell over $30,000 worth of product in a month to qualify for a $5,000 sales bonus, we can use the following IF statement:

=IF(B5 > 30000, 5000, 0)

IF Function Data Set

Since the value in cell B5 is less than $30,000, the formula evaluates to FALSE, or $0 in this case.  If the individual were to sell over $30,000, the result would look as follows:

IF Function Data Set

Note that when using the IF function, the value_if_true and value_if_false arguments only need to be bounded by quotation marks if the results are string values.  If they are numerical values, they do not need to be bounded by quotation marks.  See below:

=IF(B5 > 30000, 5000, 0) // Numerical, no quotations
=IF(B5 > 30000, “Yes”, “No”) // String, quotations

Now let’s say we want to take the first example a step further, and tier the bonus amount based on how much the individual sells within a month.  This can be done through nested IF statements.  A nested IF statement is essentially just an IF function within an IF function.  The syntax would look like:

=IF(B5 > 30000, TRUE, IF(B5 > TRUE, FALSE))

Here we see that the value_if_false in the first IF function has been replaced by another IF function.  This is what nesting IF functions looks like.  Depending on how many conditions you would like to evaluate for in your formula, you could continue to nest IF functions, as required.

Let’s now assume there are two tiers for attaining a sales bonus.  If the individual sells more than $20,000 in a month, they will earn a $2,000 bonus.  If the individual sells more than $30,000 in a month, they will earn a $5,000 bonus.  The nested IF formula would look like:

=IF(B5 > 30000, 5000, IF(B5 > 20000, 2000, 0))

IF Function Data Set

Since the individual did not sell greater than $30,000, they did not meet the first IF function criteria and it evaluated as FALSE.  The FALSE condition was another IF function, which checked to see if the individual sold more than $20,000.  Since the individual sold $25,000, this second IF function evaluated as TRUE, which resulted in the $2,000 bonus.

Notes

  • The IF function is not case sensitive
  • The IF function will be default evaluate to either TRUE or FALSE if the optional arguments are not entered
  • The IF function does not support wildcards
  • Official Documentation: For more examples and in-depth details, refer to Microsoft’s official IF function documentation

Author

Kyle Stott

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.

Want to learn more?

Join My Excel Academy Today!

This All-Access Pass Includes:

Excel Academy Bundle

30-Day Money-Back Guarantee

Want to learn more?

Join My Excel Academy Today!

Excel Academy Bundle

All-Access Pass Includes:

30-Day Money-Back Guarantee

Excel Academy Bundle

Want to learn more?

Join My Excel Academy Today!

30-Day Money-Back Guarantee

This all-access pass includes:

Don’t hesitate – join now!

Have any questions?

View our Frequently Asked Questions or contact us!