April 29, 2022

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.

The Excel IF function performs a logical test and returns one value when the condition is TRUE and another when the condition is FALSE.

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 2021, 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". This can also be set as an empty string (""), which will display a cell that looks blank.**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])`

IF is a logical function and 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? Since this is a logical function, you can find the IF function in the **Formulas **tab, **Function Library** section, under **Logical**.

The IF function is one of the most used Excel functions, and it allows you to return different values when the logical condition supplied is TRUE or FALSE. An Excel if-then formula can use the following logical operators:

Logical operators | 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.

The IF function runs 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-in functionalities that Excel recognizes automatically.

The beauty of the IF function is that it allows us to build complex financial models with lots of interdependencies and multiple logical tests. This includes using different formulas based on conditional logic, which results in powerful data analysis tools.

In our next example, we will use an IF function with multiple conditions (also called a nested IF function) to 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 Excel formula 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, the shipping fee will be zero. Otherwise, it will be calculated as 3% of the order value.

This is really easy to achieve, but it will open up many opportunities for you to use the IF function in the future.

Nesting more IF functions (also called an IF function with multiple conditions) allows you to test multiple conditions and create 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, SUMIF, 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 the formula returns `TRUE`

when either or both conditions are fulfilled.

AND allows you to supply multiple criteria to an IF statement. Basically, the IF function returns 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 function with multiple conditions will check each AND statement and return TRUE only if all are TRUE.

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:**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.

IF is a versatile function that can be used in a wide range 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? It will show you more examples of how to insert an if formula in Excel using nested IF statements and multiple conditions.

Let me know if you have questions on how to use IF function in Excel or if you need advice on how to nest multiple IF statements in your Excel project by leaving a comment below.

About me

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com

**Session expired**

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

I have numerics in J187 and K187, Date in L187. M187 is the result. I want M187=J187 if K187>0,ELSE 0. I also want M187=0, if K187>0 AND L187 is a valid date. I have tried the following formula in Google Sheet, but results are not always correct:

=IF(AND(K187>0,L1870,L187>0),0))

Kindly help if possible

Hi Suprakas,

Try this formula and let me know if it works as intended:

=IF(OR(AND(K187>0, L187>0), K187

Can I create an IF formula that contains a formula applied to two other columns? I'm looking at sales forecasts and applying a percentage based on the sales stage to determine likely revenue. For example a total deal value is $100,000 which is in column A. Column B is a drop down that lists the sales stage - proposing, negotiating, or won. I'd like expected revenue to show proposing has a 25% chance of winning, negotiating has a 50% chance, and won is 100%. So can I have a formula that is essentially IF column B = "proposing" then column C is 25% of column A? Or IF column B = "won" then column C is 100% of column A?

Hi Chris,

Sure, just adjust the cells from this formula and you should be good:

=IF(B2="Proposing", A2*25%, IF(B2="Negociating", A2*50%, IF(B2="Won", A2*100%, "")))

Let me know if that helps.

Radu

Yes,that worked. Thank you!

Hi. Can I actually use IF formula if I want to do these actions :

In C7 : I want to show : Water Usage OR Electricity Usage OR Phone Usage based on whether E7 (Water) , F7 (Electricity), G7 (Phone) is filled (E7>0 or F7>0 or G7>0).

Thank you.

Hi Atha,

Yes, you certainly can. The only question is do you only have values in one of the three cells (E7, F7, G7) at a time? Because IF statements will return the first result that corresponds to a TRUE statement.

HELP ME TO SOLVE THIS PLEASE =IF(H696.5%=100. How can i formila please healp thank you.

I'm sorry but I don't understand what you want to write. Can you please explain it in more detail?

Hi. I need help on an IF formula.

1. If cell A1, is Mon,Tue,Wed,Thu,Fri, then return value 9.5

2. If cell A1, is Sat, then return value 4

3. If cell A1, is Sun, then return value 12

4. If cell A1, if PH, then return value 12

Hi Madiha,

You can use this formula:

=IF(OR(A1="Mon", A1="Tue", A1="Wed", A1="Thu", A1="Fri"), 9.5, IF(A1="Sat", 4, IF(OR(A1="Sun", A1="PH"), 12)))

All the best,

Radu

Another option would be to use an array formula like this:

=IF(ISNUMBER(MATCH(A1, {"Mon", "Tue", "Wed", "Thu", "Fri"}, 0)), 9.5, IF(A1="Sat", 4, IF(OR(A1="Sun", A1="PH"), 12)))

If you are not using Office 2021 of Office 365, you will need to finish the formula with CTRL + SHIFT + ENTER to convert it to an array formula.