Which function returns one value if a condition is true and a different value when the condition is not true?

Excel IF Formula: Simple to Advanced

Including: Excel IF Function with Multiple Criteria, Nested IF, AND, OR Logical Excel functions

The IF() function has an important place amongst the most popular functions in Excel.

It performs a simple logical test (is a statement TRUE or FALSE?) and, depending on the comparison result, returns one value if a result is a TRUE, or another value if a result is FALSE.

The syntax of the IF() function is the following:

= IF ( logical_test , [value_if_TRUE] , [value_if_FALSE] )

The first two attributes are obligatory, i.e. without them, the formula doesn’t work, and produces an error message.

• logical _test – a placeholder for the condition we want to test

• value_if_TRUE – an attribute where we need to specify a value (text, or another function) that the IF formula returns when the result of the logical_test is TRUE

The third parameter is Optional, i.e. we can skip it and in such a case, the IF() function returns the default value FALSE.

• value_if_FALSE – an attribute for a value, text or another formula that the IF() function must return when the result of the logical test is FALSE.

In other words, IF() function is used to test the specific conditions and can be described as the statement:

IF something is true, THEN return the first value, ELSE – return then second value.

The simplest IF() function may be written with the first two parameters only.

For example, this formula is absolutely correct:

 =IF(C2=1,"Hello!")

This formula tests the condition we set (C2=1) and if it is true, the formula returns the text message (“Hello!”).

Otherwise, it returns the default value FALSE.

Interesting Fact!!! You can use another simpler test in Excel for a certain condition. Just place a formula like “=C2>C3” in a cell and press ENTER. If this statement is true, the formula returns the default value – TRUE. Else (i.e. when a value in C2 is NOT larger than a value in C3 cell) the formula will return another default value – FALSE. We use the IF() function in these cases; when we want to trigger some further actions or calculations depending on the results of the logical test.

Typically, the IF() function is used in situations when we need to calculate or flag the results based on certain conditions (for example, smaller than, equal to, greater than and others).

Despite the fact that Excel usually provides multiple choices on how to solve every specific task, the IF() function can play a good role in such cases.

Another simple example of the IF() function:

=IF(C3="Hello!",1, 0)

If a value of the C3 cell is the text “Hello!” then this formula returns the one (1) value.

Else (i.e. if this cell contains any other text, number or blank value), the formula returns zero.

The big advantage of the IF() function (like many other Excel functions) is how we can replace all three arguments in this example with whatever we want/need and it still works properly (if we follow the syntax, of course).

For example, let’s replace the one (as a value_if_true) to another formula “C3+C4” and replace zero value (0) to TODAY() function.

Our initial formula becomes:

=IF(C3="Hello!",C2+C3,TODAY())

That means – IF a value in C2 cell equals one, THEN do the addition C2+C3.

ELSE (i.e. when C2 doesn’t equal “Hello!”), THEN execute the TODAY() function and return its result.

IF() function is a universal function, i.e. it accepts as an attribute different types of a value, such as numeric value, text, date/time, other functions, named ranges and others).

The IF function can be easily used as a part of other more complex formulas.

Moreover, you can use the so-called nested IF() function and replace the [value_if_false] with another IF() function.

The most common reason to do that is when you need to create a multi-choice comparison like a waterfall.

The last value_if_FALSE attribute is a value that we want the formula to return if the result of ALL logical tests in the formula is FALSE.

We know that we need to use a nested IF() (i.e. one IF() inside another IF() statement) when we have a “but” keyword inside the sentence with a logical test.

Take a look at the screenshot below.

In this example, we need to add the text “flag” if a value is greater than 200, BUT if this value is less than 50 we need to return “follow up” text.

We know that we need to use a nested IF() when there is the “but” keyword in the logical test.

Pro Tip: Be cautious. The more IF iterations we use, the more complex the formula becomes and the more difficult it is to understand. In addition, the complex formulas that contain multiple IF functions along with other Excel functions may slow down the calculation speed, especially if they use large ranges of cells. Also, it’s very easy to make an accidental mistake or typo inside the long and complex formulas, and such errors are difficult to trace properly.

The IF() function is often used with other logical functions such as AND() or OR() functions that we will discuss further.

Let’s look at some practical examples…

TEST CASE №1Adding the word “Good”  if the Revenue is greater than 15000

This is a typical use of the IF() function.

Let’s flag all apps if their Revenue is above 15,000.

Step 1. Go to the first cell where we need to insert a formula (cell C5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the opened parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF() function is a logical test.

In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000.

So, let’s continue the formula =IF(B5>15000,

Do not forget to put a comma once this step is ready.

This coma is used by Excel as a delimiter to separate the arguments from each other.

Pro tip: We can either type a numeric value in the logical test or use a cell reference to a cell that contains such a value (C2 cell in our example). The second approach is preferred because it gives us more flexibility in the future. Once we copy/paste the formula to the entire range, all we need to do in this case – if want to change that value – is go to the referral cell and change it there without needing to re-write the formula for all related cells.

NOTE: Keep in mind that if we want to use this approach, we need to fix the cell reference to look like $C$2. This fixed cell address is required for copying the formula to the entire range. Without that, the formula will return the wrong result. We can simply press F4 to fix the cell reference in the formula (make sure that the cursor is located near the appropriate cell address)

The formula becomes:

=IF(B5>$C$2,

Step 3. The second argument of IF() function, as we remember, is a value_if_TRUE.

Let’s type the text “Good” as a second attribute in the formula:

=IF(B5>$C$2, "Good",

Do not forget to put a comma once this step is ready.

Also, be careful – if you type a text value, you need to put it inside a double quotation mark (“your text”)

Pro tip: We can either type a numeric value in the logical test or use a cell reference to a cell that contains such a value (C3 cell in our example). If in the future we’ll decide to change the “Good” word to something else (“Not Bad”, for example), then all we need to do is go to cell C3 and change that text in the cell without the necessity to re-do the entire formula.

Our formula becomes:

=IF(B5>$C$2,$C$3,

NOTE: Don’t forget to fix the cell reference to look like $C$3 address. Press F4 to make an absolute reference. Without that, the formula will return a wrong result.

Step 4. The third argument of the IF() function is value_if_FALSE. We can skip this attribute (in such cases Excel returns the default value – FALSE). However, if we want to do that in a more aesthetic way, let the formula return a blank value. The blank value in Excel is equal to nothing between two quotation marks, like “”. So, the final version of the formula becomes:

=IF(B5>$C$2,$C$3,"")

NOTE: Don’t forget to close the brackets and press ENTER.

Step 5. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

TEST CASE №2Adding the word “Good”  if the Revenue is greater than 15000 and less than 20000

Let’s flag all apps if their Revenue is above 15,000 but under 20,000

Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the opened parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF() function is a logical test.

In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000 and less than 20,000.

This is a typical situation when we can use the AND() logical function to unite several certain conditions into a single logical test.

AND() function can accept up to 255 conditions as its arguments.

Let’s continue the formula and add the second keyword AND with an opened parenthesis.

Once we’ve done that, we can type both conditions for the logical test inside AND() block and close its second parenthesis.

The formula looks like:

=IF(AND(B5>$D$2,B5<$D$3),

NOTE: Don’t forget to fix the cell reference to look like $D$2 and $D$3 address.

Press F4 to make an absolute reference.

Without that, the formula will return a wrong result after we drag it down to the entire range.

NOTE: Don’t forget to use a comma once this step is ready.

This comma is used by Excel as a delimiter to separate the arguments from each other.

Step 2. The second argument of IF() function, as we remember, is a value_if_TRUE.

Let’s add another cell reference as a second attribute in the formula:

=IF(AND(B5>$D$2,B5<$D$3),$C$3,

NOTE: Don’t forget to put a comma after the attribute once this step is ready. Be careful. If you type a text value, you need to put it inside a double quotation mark (“your text”)

Step 3. The third argument of the IF() function is a value_if_FALSE.

We can skip this attribute and Excel returns the default value – FALSE.

However, the more aesthetic way to do that is to return nothing (or blank value).

Nothing in Excel is denoted as a double quotation mark without any space:

=IF(AND(B5>$D$2,B5<$D$3),$C$3,"")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 4. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

TEST CASE №3Adding the word “Good”  if the Revenue is greater than 15000 and less than 20000. Adding the word “Exceptional” if the Revenue is greater than or equal to 20,000. Otherwise, keeping the values

This time we’ll flag all apps as “Good” if their Revenue is above 15,000 and under 20,000.

In addition, we’ll flag them as “Exceptional” if their Revenue is greater than or equal to 20,000

Step 1. Go to the first cell where we need to insert a formula (cell E5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF() function is a logical test.

The first part of the formula in this example is the same as we wrote in the previous example:

=IF(AND(B5>$E$2,B5<$E$3),$C$3,

NOTE: Don’t forget to to put a comma after the attribute once this step is ready.

Step 3. Usually, the third argument of the IF() function is a value_if_FALSE.

However, this time we need to add the second condition when a value in cell B5 is greater than or equal to 20,000.

We can do that if we use nested IF() function.

Let’s add another IF() block as a third parameter now:

=IF(AND(B5>$E$2,B5<$E$3),$C$3,IF(

Step 4. Add the second logical test, and the second value_if_TRUE.

In our example, the formula must return the “Exceptional” keyword if the value in cell B5 is greater than or equal to 20,000.

Let’s type this:

=IF(AND(B5>$E$2,B5<$E$3),$C$3,IF(B5>=$E$3,"Exceptional",

NOTE: If you type a value as a text, you need to put it inside a double quotation mark (“your text”)

Step 5. We need to write a value that the formula will return if the result of both logical tests is FALSE.

In our example, the formula must return the raw Revenue value in this case.

Let’s place this attribute as the third argument in the second IF() block:

=IF(AND(B5>$E$2,B5<$E$3),$C$3,IF(B5>=$E$3,"Exceptional",B5))

NOTE: Don’t forget to close the brackets.

We created two IF blocks, so we need to close two brackets at the end.

Step 6. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

TEST CASE №4Adding the word “Flag”  if the Revenue is greater than or equal to 20,000 OR it is lesser than or equal to 15,000

Let’s flag all apps if their Revenue is <= 15,000 or >= 20,000

Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF() function is a logical test.

In this example we want to flag a value in cell B5 (Revenue) if it is below or equal to 15000 OR above or equal to 20,000.

This is a situation when we can use OR() logical function that allows the testing of multiple logical conditions and return a true value if ANY of them is true.

Let’s continue the formula and add the second keyword OR with an opened bracket.

Once we’ve done that, we can type both conditions for the logical test inside OR() block and close its second parentheses.

The formula looks like:

=IF(OR(B5>=$F$2,B5=<$F$3),

NOTE: Press the F4 button on the keyboard to make an absolute reference.

Step 3. The second argument of the IF() function, as we remember, is a value_if_TRUE.

Let’s add this attribute in the formula:

=IF(AND(B5>$D$2,B5<$D$3),"Flag",

NOTE: If you type a text value, you need to put it inside a double quotation mark (“your text”)

Step 4. The third argument of the IF() function is a value_if_FALSE.

Let’s add a double quotation mark without any space to return a blank value:

=IF(AND(B5>$D$2, B5<$D$3),"Flag","")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 5. Drag the formula down to the entire range.

Quickly check to ensure that the formula returns the correct result.

TEST CASE №5Using other formulas inside the logical functions

Let’s calculate the difference between Actual Revenue and Budget Revenue and flag those cases when the difference is larger than +/- 10%.

Step 1. The easiest way to start building such a complex formula is to calculate the core value (this is the +/-10% threshold value in our case).

Step 2. Go to the first cell where we need to insert a formula (cell D23 in this example).

Start the formula with an equal sign (=), add the formula to calculate the difference (Actual divided by Budget minus one:

=B23/C23 - 1

Step 3. Let’s add the IF() block and the appropriate logical test.

This is a situation when we can use the OR() logical function as we need to flag a value if it is greater than +10% or lower than -10%:

=IF(OR(B23/C23 - 1>10%,B23/C23 - 1<-10%),

Step 4. The second argument of the IF() function, as we remember, is a value_if_TRUE.

In this example we need to return the exact deviation, so let’s do that, by simply copying the initial formula.

=IF(OR(B23/C23 - 1>10%,B23/C23 - 1<-10%),B23/C23 - 1,

Step 5. The third argument of the IF() function is a value_if_FALSE.

Let’s add a double quotation mark without any space to return a blank value:

=IF(OR(B23/C23 - 1>10%,B23/C23 - 1<-10%),B23/C23 - 1,"")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 6. Drag the formula down to the entire range.

Quickly check to ensure that the formula returns the correct result.

TEST CASE №6Using text symbols as the result of the IF() function

Let’s mark the positive deviation with the up arrow and the negative deviation with the down arrow.

Step 1. We’ll continue the previous example.

Let’s say we want to mark all positive deviations with a symbol that looks like an up arrow and all negative deviations with a symbol that looks like a down arrow.

First of all, a few preparations.

Let’s go to the cells above the test range (A20 and A21 cells in this example).

Feel free to select any other cells if you prefer.

Step 2. Insert the symbols that will indicate all positive and negative deviations.

To do this, we need to go on the INSERT tab on the Excel Ribbon and follow the path: INSERT > Symbol > Arial > Geometric Shapes

Click Insert once you have selected a shape.

Go to another cell below and insert the second arrow in the same way.

Step 3. Go to the first cell in the range where we want to add these visual indicators.

Replicate the IF() function with the cell references containing the arrows as the value_if_TRUE and value_if_FALSE attributes.

=IF(B23/C23 - 1>10%,$A$20,IF(B23/C23 - 1<-10%,$A$21,""))

NOTE: Don’t forget to close the bracket and press ENTER.

Step 4. Drag the formula down to the entire range.

Quickly check that the formula returns the correct result.

Workbook for Download

Feel free to Download the Workbook HERE.

Excel ESSENTIALS for the REAL World (The Complete Excel Course)

From Excel Beginner to Professional

Learn Excel from Scratch

OR Improve Your Excel Skills to Become More Confident

Check out our best-selling course

44 Comments

1. saif April 26, 2018 at 8:03 pm

   • Leila Gharani April 26, 2018 at 8:22 pm

     You’re very welcome Saif.

2. nasir garba sule May 16, 2018 at 2:52 pm

   thank you for ythe sharing

   • Leila Gharani May 23, 2018 at 5:40 pm

     You’re very welcome Nasir.

3. Samuel August 8, 2018 at 1:23 pm

   • Leila Gharani August 18, 2018 at 6:34 pm

     You’re very welcome Samuel.

4. Sandeip Shukla August 23, 2018 at 6:11 pm

   Hello ma’am your tutorials videos and the way you present your whole content is amazing. I want to be like you, an excel expert what should I do? your teaching skills are great.

   • Leila Gharani August 23, 2018 at 7:00 pm

     Thank you Sandeip. I’m glad you like the tutorials. I started by answering other people’s Excel questions, reading books, taking courses, using what I learnt at work and then making videos….

5. Asad September 14, 2018 at 6:47 am

   Thanks for your contribution.

6. KERİ September 15, 2018 at 3:09 pm

   WHERE İS WORKBOO FRO DOWLOADİNG

   • Leila Gharani September 20, 2018 at 3:33 pm

     Hi – it’s at the bottom of the post – you’ll see a workbook icon there.

7. gigs September 16, 2018 at 1:42 am

   NSC Last 3 Months Without 13th Month Persistency

   50,000 -99,999 12%
   100,000 – 199,999 14%
   200,000 – 299,999 18%
   300,000 – 499,999 23%
   500,000 and up 30%

   Good day. above is the sample range. would like to ask help on how to to put it in a cell that the result will be either of the percentage if any of the ranges will appear?

   thanks

   roderick

   • Chayan Chakraborty August 4, 2019 at 3:05 pm

     Thank you very much. The video was very helpful.

8. Khadar October 11, 2018 at 12:55 pm

   Hello Leila,

   Excellent teaching skills…

   • Leila Gharani October 11, 2018 at 2:14 pm

     Thank you! I’m glad you find the tutorial easy to follow.

9. Anjum October 14, 2018 at 6:06 pm

   Thank you! Amazing and well-explained videos. Really useful indeed.

10. Ershadul Hoque October 31, 2018 at 8:55 am

    Is there any option/formula to find out the lowest or highest value from 3 number/column?

    • Leila Gharani November 7, 2018 at 5:46 pm

      You can use the MAX function here. Type in =Max(highlight the range of numbers)

11. Masood December 19, 2018 at 7:44 pm

    very well explained. I just got stuck into a calculation on excel. searched the youtube and found it. great job Leila.

    • Leila Gharani December 20, 2018 at 9:09 am

12. manyuckis January 18, 2019 at 1:41 pm

    Hi, are there any more workbooks to practice on? thanks on the tutorial by the way. it helps a lot.

    • Leila Gharani January 18, 2019 at 1:59 pm

      I’m glad you like the tutorial. I’m not sure if you’re registered for my advanced Excel course but there is an exercise workbook there for all the functions we learn (answers included on separate tabs).

13. Suzana January 27, 2019 at 6:35 pm

    • Leila Gharani January 28, 2019 at 12:03 pm

      You’re very welcome Suzana :)

14. Raja February 2, 2019 at 7:13 pm

    Thank you leila for your excellent tutorial like excel dashboard.I would like to learn exclusive advanced excel dashboard from you with all advanced functions and tools used.Could you please send me all details in my email with affordable price so that i could able to pay the amount.

    • Leila Gharani February 9, 2019 at 6:08 pm

      Hi Raja – you can find all the information on the course here. Thank you for your support.

15. John Maina Gatonye February 5, 2019 at 10:50 am

16. Alfred March 14, 2019 at 4:51 am

    Never learnt so much in such a short time. Thanks Leila for keeping your tutorials so simple and easy to understand. Your Index/Match video was very helpful. How do i subscribe for the advanced course?

    • Bryon Smedley April 7, 2019 at 2:07 pm

17. Vadivelan March 21, 2019 at 11:57 am

    Hi Leila,
    I have issue on excel formula, can please solve the issue.

    I have done stock count in excel for count 1 and count 2, the formula i set is working but i set for count 3 is not working. below is the detail.
    A B C
    BOOKED QTY COUNT 1 COUNT 2 FINAL QTY
    30 29 30 30 The formula is =IF(C1=0,B1,C1) is working but

    If i used same formula for count 3 is not working.
    A B C D
    BOOKED QTY COUNT 1 COUNT 2 Count 3 FINAL QTY
    30 28 29 30 30 The formula is =IF(D1=0,B1,C1,D1)

    Please solve this issue, Thanks

    • Bryon Smedley April 7, 2019 at 7:48 pm

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      XelPlus Team

18. Tamer May 3, 2019 at 6:09 am

    Very good
    I learned a lot from you thanks for advanced excel
    Waiting for next one

19. Jörg Weber May 11, 2019 at 2:31 pm

    Thanks a lot also from me.
    For me it is a little bit harder to leran because I have only School english. But I can understand you very well and so I learn very good.
    Thanks a lot

20. Sonam Dhendup May 19, 2019 at 8:16 am

    thank you….. i have learned so many things from your videos…..
    can you please help me to solve my problems….

    I have default value in Cell D12

    when i ender Value of Cell D20 the value of D20 should be added with D12 and want the result in in C12 and

    when i ender Value of Cell D21, the value of D21 should also be added with D12 and want the result in in C12

    at the same time (in same table)

    if i enter value in Cell D26, the value of D26 should be subtracted from the value of D12 and and want the result in C12

    if i enter value in Cell D27, the value of D27 should be subtracted from the value of D12 and and want the result in C12

    in the same data table

    in different cell i am able to get the result but i could not solve this problem to get the result in same cell i.e. C12

    • Chris August 23, 2019 at 8:17 pm

      Thank you for your comment. We receive a lot of questions daily and as much as we’d like to answer all of them, it’s unfortunately not possible.
      However, to make sure you get a response to your Excel query, it would be best to post your question on the Microsoft’s Tech Community Platform for Excel. This way you can get answers from other Excel experts.

21. • Leila Gharani June 19, 2019 at 2:38 pm

      You’re very welcome Kishore :)

22. Medhat July 29, 2019 at 11:19 am

    Please can you look into this formula
    Subject : I want to tell excel that if I5 = one condition for example , CAS , you should apply the function VLOOKUP and take the criteria in G5 and returns the value ahead of this criteria from another file from the column CAS, otherwise if I5 = another condition , for example PCS, you should take the criteria in G5 and returns the value lies in the column of PCS in that file, and finally, if I5 = PKT, you should apply VLOOKUP function and search for the criteria G5 and returns the result in the column of PKT in that file there
    The formula : =IF(AND(I5=”CAS”),VLOOKUP(G5,'[Updated Price Structure 10 2018 RIYAZ.xlsx]MASTER ITEM FILE’!$B:$N,13,0)+IF(AND(I5=”PCS”),VLOOKUP(G5,'[Updated Price Structure 10 2018 RIYAZ.xlsx]MASTER ITEM FILE’!$B:$O,14,0)+IF(AND(I5=”PKT”),VLOOKUP(G5,'[Updated Price Structure 10 2018 RIYAZ.xlsx]MASTER ITEM FILE’!$B:$P,15,0))))
    The result : Excel considers the first condition only and VLOOKUP have been worked and give the results for the first condition only I5 = “CAS”, meanwhile the second and third conditions : if I5 =”CAS” or “PCS”, the formula VLOOKUP have not been worked and it gives FALSE result …. that means VLOOKUP formula worked since the first condition have been met only ….
    Required : How can I fix a formula like that …. Many conditions to apply VLOOKUP Function and every condition depend on what is I5 = CAS, or PCS, or PKT
    Thanks

    • Chris August 23, 2019 at 7:02 pm

      Hi Medhat, best would be to post your specific question with screenshots on an Excel forum – for example Microsoft’s Tech Community right here. The Excel Tech Community has some of the finest minds in the industry. With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able. I’m certain someone there can inform you of the best way to reach your solution.

23. joseph October 6, 2019 at 4:11 pm

    =IF(B9=”MTB”,”7:00″,IF(B9=”YA”,”7:00″)) when i use this formula i m not getting if it is wrong…
    can you pls help me out this

    • Chris October 9, 2019 at 11:12 am

      You are missing the Else-Statement. What if B9 does not equal MTB or YA? Which value should be shown.

24. Anand October 29, 2019 at 11:02 pm

    Thank you, Leila.

    I learn from your excel youtube as well as I am subscribed. Highly recommended. Please may I know how different will be the course, will you teach VB, macros as well?

    • Leila Gharani October 30, 2019 at 7:56 pm

      Thank you. There is a separate course for VBA and macros.

25. banuka November 2, 2019 at 6:38 pm

    Thanks a lot to help us to become more knowledgeable in Exce.l

Comments are closed.