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. =IF(logical_test, value_if_true, value_if_false).
    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. Step 1 =IF(B3>C3, "Goal Met", "Goal Not Met").
    [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. Step 2 =IF(B3=C3, "Goal Met").
    • 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. Step 3 =IF(B3*2>C3, C3*400, "Good").
    • 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. Step 4 =IF(B3="Sold", "1", ""
    )
    • 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. Step 5 =IF(D3="Taxed", F3*.07, "0").
    • 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. Step 6 Things to remember.
    • 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. Step 1 You can nest up to 64 IF/THEN statements in a single formula.
    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. Step 2 Nesting many IF/THEN statements can be challenging.
    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. Step 1 The result is zero (0).
    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. Step 2 The result is the #NAME?
    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

Google Sheets How to Insert Time in Cell Automatically4 Easy Ways to Add the Time and Date Automatically in Sheets
Use Google Spreadsheets
How to
Use Google Sheets: The Ultimate Guide for Getting Started
Add the Same Value to Multiple Cells in Excel
How to
Copy the Same Text to Multiple Cells in Excel: Desktop & Mobile
Sort by Number on Google Sheets on PC or MacThe 3 Easiest Ways to Sort by Number in Google Sheets
Add a Custom Field in Pivot Table
How to
Add Custom Calculated Fields to Pivot Tables in Excel
Add Rows to a Pivot Table
How to
Add Rows to a Pivot Table
Get Rid of Dotted Lines in Excel3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
Embed Documents in ExcelEasily Embed Documents and Files in an Excel Spreadsheet
Change X Axis Scale in Excel
How to
Change the X Axis Scale in an Excel Chart
Make a Google Spreadsheet
How to
Make a Google Spreadsheet: Beginners Guide
Add Two Cells Containing the Sum of Other Formulas in Excel
How to
Add Two Cells Containing the Sum of Other Formulas in Excel
Add a Row to a Table in Excel
How to
Add a Row to a Table in Excel
Make a Shared Spreadsheet3 Simple Ways to Create a Shared Spreadsheet
Change an Excel Sheet from Read Only
How to
Change an Excel Sheet from Read Only
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

Nicole Levine, MFA
Written by:
Nicole Levine, MFA
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions. This article has been viewed 137,300 times.
How helpful is this?
Co-authors: 4
Updated: June 17, 2024
Views: 137,300
Categories: Spreadsheets
Article SummaryX

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 137,300 times.

Is this article up to date?

Advertisement