How To Create A If Function In Excel

This is a step-by-step guide on how to use IF function in Excel. It shows you how to create a formula using the IF function, 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:

Select the cell where you want to insert the IF formula. Using your mouse or keyboard navigate to the cell where you want to insert your formula.
Type =IF(
Insert the condition that you want to check, followed by a comma (,). The first argument of the IF function is the logical_test. This is the condition that you want to validate. For example C6 > 70.
Insert the value to display when the condition is TRUE, followed by a comma (,). The second argument of the IF function is value_if_true. Here, you can insert a nested formula or a simple message such as "YES".
Insert the value to display when the condition is FALSE. The last argument of the IF function is value_if_false. Just like the previous step, you can insert a nested formula or display a message such as "NO".
Type ) to close the function and press ENTER

The following video shows you exactly how to apply the six steps described above and create your first IF formula.

The syntax that shows how to create an IF function in Excel is explained below:
=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).

Where is the IF function in Excel? You can find the IF function in the Formulas tab, Function Library section, under Logical functions.

Logical operators for IF function

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.

Logical operator	Definition	Example
=	equal to	A1=B1
<>	not equal to	A1<>B1
>	greater than	A1>B1
>=	greater than or equal to	A1>=B1
<	lower than	A1<B1
<=	lower than or equal to	A1<=B1

The IF function doesn't support wildcards.

Your first IF formula

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:
logical_test: E9>=70
value_if_true: Pass is returned if E9>=70.
value_if_false: 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.

How to use IF in Excel with another function or formula

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%)

Writing an IF formula with another function

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.

How to use nested IF statements in Excel

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.

How to use IF formula with OR function in Excel

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.

How to use IF formula with AND function in Excel

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.

How to use IF function with VLOOKUP in Excel

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:
logical_test: G152="No"
value_if_true: VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152 is used to retrieve the corresponding shipping fee percentage when G152="No"
value_if_false: 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.

What to do next?

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.