How to Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More

PDF download Download Article
Plus, examples & troubleshooting tips to help organize any spreadsheet
PDF download Download Article

One of the most useful features of Microsoft Excel is the IF-THEN statement, which is used to perform calculations or display text based on whether a condition is true. If you're confused about the IF function, this handy wikiHow tutorial will guide you through writing useful IF-THEN formulas, from making simple comparisons to testing multiple conditions. Read on for examples, troubleshooting tips, and more!

Creating an Excel If-Then Formula

  • Use the syntax =IF(logical_test, value_if_true, value_if_false).
  • “Logical_test” is the condition you want to test.
  • “Value_if_true” is the value you want returned if “logical_test” is true.
  • “Value_if_false” is the value you want returned if “logical_test” is false.
Section 1 of 4:

IF-THEN Syntax

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/69\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-1.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-1.jpg","bigUrl":"\/images\/thumb\/6\/69\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-1.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    An IF statement makes a logical comparison based on whether a condition is true or false. [1]
    • logical_test: This is the condition that you want to test (required).
    • value_if_true: The value you want returned if logical_test is true (required).
    • value_if_false: The value you want returned if logical_test is not true (optional).
    2. Advertisement
Section 2 of 4:

Simple Examples

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/c3\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-2-Version-2.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-2-Version-2.jpg","bigUrl":"\/images\/thumb\/c\/c3\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-2-Version-2.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-2-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    [2]
    • In this example, the condition is B3>C3 , meaning "If the value of B3 is greater than C3."
    • If the value of B3 is greater than C3, then Goal Met will appear in the cell.
    • If the value of B3 is less than the value of C3, then Goal Not Met will appear in the cell.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/62\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-3-Version-2.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-3-Version-2.jpg","bigUrl":"\/images\/thumb\/6\/62\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-3-Version-2.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-3-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • In this example, the condition is B3=C3 , meaning, "If the value of B3 is equal to the value of C3."
    • If the values are equal, "Goal Met" will appear in the cell.
    • If the values are not equal, a zero will appear in the field because we did not define a third (ELSE) value. [3]
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/50\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-4.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-4.jpg","bigUrl":"\/images\/thumb\/5\/50\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-4.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • In this example the condition is B3*2>C3 , meaning "If the value of B3 multiplied by 2 is greater than the value of C3."
    • If the value of B3*2 is greater than the value of C3, the value of C3 multiplied by 400 will appear in this cell.
    • If the value of B3*2 is not greater than the value of C3, the word "Good" will appear in the cell.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/57\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-5.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-5.jpg","bigUrl":"\/images\/thumb\/5\/57\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-5.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-5.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    )
    • In this example, the condition is B3="Sold" , meaning "If the value of cell B3 contains the word Sold ."
    • If B3 contains the word "Sold," a "1" will appear in the cell.
    • If B3 says anything other than the word "Sold," the condition is false. Since our ELSE value is " " , which is two double quote marks with nothing in between, the cell value will be blank.
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/fc\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-6.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-6.jpg","bigUrl":"\/images\/thumb\/f\/fc\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-6.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-6.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • In this example, the condition is D3="Taxed" , meaning "If the value of D3 is the word Taxed ."
    • If D3 contains the word "Taxed," the result will be the value of F3 multiplied by .07.
    • If D3 contains anything other than the word "Taxed," the result will be 0.
  6. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/1b\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-7.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-7.jpg","bigUrl":"\/images\/thumb\/1\/1b\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-7.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-7.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • An IF statement can have two different results—one result if the condition is true, and another result if the condition is false. [4]
    • The ELSE value, which is what will happen if the result is false, is optional—if you don't specify what to place in the cell if the condition is false, a "0" will appear in the cell. If you'd rather the cell be blank, make your ELSE value "" .
    • When referring to specific text in a condition or THEN/ELSE value, always surround that text in quotation marks.
    7. Advertisement
Section 3 of 4:

Testing Multiple Conditions

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/21\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-8.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-8.jpg","bigUrl":"\/images\/thumb\/2\/21\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-8.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-8.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    For example, let's say you want to create a formula that calculates sales tax for purchases made from Alaska, California, Oregon, and Washington. Since each of these four states has its own tax rate, we'll need to create a formula that contains four IF/THEN statements to test four conditions. Assuming the sale amount is in G1 and the purchaser's state is in F1, our formula would look like this, using the 2022 tax rates for these 4 states:
    • =IF(F1="Alaska", G1*0,IF(F1="California",G1*0.0725,IF(F1="Oregon",G1*0,IF(F1="Washington", G1*0.065))))
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/b\/b1\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-9.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-9.jpg","bigUrl":"\/images\/thumb\/b\/b1\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-9.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-9.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Aside from the very long formulas, creating very long IF/THEN statements may become cumbersome if you have to frequently change them. In our previous example, we'd need to update our IF/THEN statements any time a state's tax rate changed. [5]
    • A good alternative to IF/THEN statements in this situation would be to add the values to a single table and use a VLOOKUP formula to query the table for the proper rates.
    • For example, if we add a list of our states to column A and their corresponding tax rates in column B, we could write a VLOOKUP formula that does the math for us without specifying the amounts in the formula itself. The shortened version of the above nested IF/THEN formula would be =G1*VLOOKUP(F1,A:B,2,TRUE) .
    • If you had to update a tax rate, you'd now only have to update it in the referenced table, not all of your formulas.
    3. Advertisement
Section 4 of 4:

Troubleshooting

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/54\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-10.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-10.jpg","bigUrl":"\/images\/thumb\/5\/54\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-10.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-10.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    This means that you didn't specify a value for either the value_if_true or value_if_false arguments. [6]
    • If you don't specify a value_if_false value, the result will always be 0 if the condition is false.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/d\/d4\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-11.jpg\/v4-460px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-11.jpg","bigUrl":"\/images\/thumb\/d\/d4\/How-Do-You-Write-an-if-then-Formula-in-Excel-Step-11.jpg\/v4-728px-How-Do-You-Write-an-if-then-Formula-in-Excel-Step-11.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    error. This indicates that a syntax error—make sure you've surrounded the formula after = with parentheses, your arguments are separated by commas, and that any plain text is surrounded by quotation marks. [7]
    • For example, this IF/THEN statement will result in the #NAME? error because there are no quotation marks around the value OK : =IF(A2>1,OK) .
    • The correct syntax would be =IF(A2>1,"OK")
    3. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      4 Easy Ways to Add the Time and Date Automatically in Sheets
      The 3 Easiest Ways to Sort by Number in Google Sheets
      How to
      Copy the Same Text to Multiple Cells in Excel: Desktop & Mobile
      3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
      How to
      Add Custom Calculated Fields to Pivot Tables in Excel
      How to
      Add Rows to a Pivot Table
      How to
      Use Google Sheets: The Ultimate Guide for Getting Started
      How to
      Change the X Axis Scale in an Excel Chart
      Easily Embed Documents and Files in an Excel Spreadsheet
      How to
      Add a Row to a Table in Excel
      How to
      Add Two Cells Containing the Sum of Other Formulas in Excel
      3 Simple Ways to Create a Shared Spreadsheet
      How to
      Make a Google Spreadsheet: Beginners Guide
      How to
      Show the Max Value in an Excel Graph
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
      2. ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
      3. ↑ https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
      4. ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
      5. ↑ https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
      6. ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
      7. ↑ https://support.microsoft.com/en-us/office/how-to-correct-a-name-error-b6d54e31-a743-4d7d-9b61-40002a7b4286

      About This Article

      Article Summary X

      1. The syntax for an IF-THEN statement is =IF(logical_test, value_if_true, value_if_false).
      2. "logical_test" is the condition you want to test, such as whether a cell value is greater than or equal to another cell.
      3. "value_if_true" is what the formula should do if the test condition is true.
      4. "value_if_false" is what the formula should do if the test condition is "not" true.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 133,772 times.

      Is this article up to date?

      Advertisement