This is a step-by-step guide on how to use IF function in Excel. It includes several IF formula examples, an introduction on how to use nested IF formulas, and the exercise file I used when creating this tutorial.
So, how do you write an if-then formula in Excel? Well, the syntax for IF statements is the same in all Excel versions. This means that you can use any of the examples shown in this article in Excel for Microsoft 365 or Excel 2019, 2016, 2013, 2010, 2007, and 2003.
How to use IF function in Excel:
The following video shows you exactly how to apply the six steps described above and create your first IF formula.
IF function syntax:
=IF(logical_test, [value_if_true], [value_if_false])
Using the function implies setting 3 arguments:
logical_test - The logical condition that you want to test. This will return either a TRUE or a FALSE value.
value_if_true - [optional] The value or formula which will be used when logical_test is TRUE.
value_if_false - [optional] The value or formula which will be used when logical_test is FALSE.
Please remember that while both value_if_true and value_if_false are optional, at least one of them needs to be supplied. Otherwise, your IF formula will simply return 0 (zero).
The IF function is one of the most used functions in Excel, and it allows you to return different values when the logical condition supplied is TRUE or FALSE. Below you can see a list of logical operators that can be supplied in your IF formula.
|<>||not equal to||A1<>B1|
|>=||greater than or equal to||A1>=B1|
|<=||lower than or equal to||A1<=B1|
The IF function doesn't support wildcards.
The IF function is used to run a logical test, and returns different values depending on whether the result is TRUE or FALSE. The result from IF can be a value, a cell reference, or even another formula.
Now let’s move on to some examples.
We’ll be evaluating exam grades. If the student obtained a score higher than or equal to 70, then we will return the message "Pass." If the grade is lower than 70, then we will display "Fail."
In this example, I have inserted the following formula in cell F9:
=IF(E9>=70, "Pass", "Fail")
The 3 arguments for this IF formula are:
Pass is returned if E9>=70.
Fail is returned if E9<70.
Please note that when you want to use text in your IF formulas (like a word or sentence) you need to wrap the text in quotes (e.g. “Fail”). The only exception is while using TRUE or FALSE, which are built into Excel and recognized automatically.
The beauty of the IF function is that it allows us to build complex financial models with lots of interdependencies. This includes using different formulas based on conditional logic.
In our next example, we will calculate a payment fee based on the value of the order. If the order value is higher than or equal to $1000, then it should calculate a payment fee of 1.00%. However, if the total order value is lower than $1000, then it should use 1.50%.
The formula I’ve used in cell F31 is:
=IF(E31>=1000, E31*1%, E31*1.5%)
Now let's look at an IF formula that is dependent on user input. If we select free shipping for the order, then the shipping fee will be set to zero. Otherwise, it will be calculated as 3% of the order value.
This is something really easy to achieve, but it will open up so many opportunities for you to use the IF function in the future.
Nesting more IF functions allows you to create even more complex formulas. However, you can only nest up to 64 IF functions in Excel. If you ever reach this limit (I never did), I can guarantee that there is a better and more elegant solution using functions like VLOOKUP, SUMIFS, or COUNTIFS.
In the next example, I wrote a formula with several nested IF functions to assign a grade to a list of students based on their test results.
=IF(E71<60, "F", IF(E71<70, "D", IF(E71<80, "C", IF(E71<90, "B", "A"))))
The order of the conditions is important. When the conditions overlap, Excel will retrieve the [value_if_true] argument from the first IF statement that returns TRUE. This is why the conditions from the formula above need to be inserted in the same order for the formula to work properly.
Note: If you are running Office 365, then you can also look at the new IFS function. This function runs multiple tests and returns the value corresponding to the first TRUE result. It's a very useful alternative to nested IF formulas and makes your formulas much easier to understand by others. You can read more about IFS on Microsoft's website.
OR allows you to supply alternative conditions to an IF statement. This opens up opportunities to create complex scenarios where certain behavior is triggered by multiple possible conditions.
Let's look at an IF formula that calculates a 2.00% shipping fee when the total order value is higher than $1000 or when there are more than 5 items in the order.
The IF OR statement I've used in cell H106 is:
=IF(OR(G106>1000, F106>5), G106*2%, 0)
The OR function evaluates if
G106>1000 or if
F106>5 and returns
TRUE when either or both conditions are fulfilled.
AND allows you to supply multiple cumulative conditions to an IF statement. Basically, the IF formula will return TRUE if, and only if, all the conditions are met.
Working with our previous example, let's apply the shipping fee only when the total order value is higher than $1000, and the order contains more than 5 items.
The IF AND statement I've used in cell H106 is:
=IF(AND(G128>1000, F128>5), G128*2%, 0)
The AND function evaluates if
G106>1000 and if
F106>5 and returns
TRUE when both conditions are fulfilled.
VLOOKUP can be nested inside an IF formula to retrieve data when a condition is TRUE or FALSE. In the next example, I will show you how to calculate shipping fees based on a different table that contains the thresholds and percentages to be applied depending on the order value.
The formula I've used in cell F152:
=IF(G152="No", VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152, 0)
The formula uses the following arguments:
VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152 is used to retrieve the corresponding shipping fee percentage when G152="No"
0 is returned if G152 is anything else than "No." In our case, the alternative is selecting "Yes" from the drop-down list.
Note: One thing to remember is that I've used a VLOOKUP formula with an approximate match argument. This means that your data must be sorted in ascending order by lookup value (in our case, the Order amount).
In case you need additional help, please also read this article that explains step by step how to use VLOOKUP function in Excel.
IF is a versatile function that can be used in a wide variety of scenarios. I use it daily, and I can't imagine a world where Excel would lack this functionality.
Practice writing formulas using the IF function, and your spreadsheets will definitely get better and more complex. For example, why not look at another example using an IF function with 3 conditions?
Let me know if you have questions on how to use IF function in Excel or if you need advice using IF in your Excel project by leaving a comment below.