How to Use the Excel IF/THEN Formula (with Examples)

Key Takeaways

• The IF function in Excel uses the syntax =IF(logical_test, value_if_true, value_if_false) to return different results based on a condition.

• You can nest multiple IF formulas inside one another to evaluate several conditions and return tiered results.

• Combining IF with AND or OR lets you test multiple criteria within a single logical test.

• Common IF formula errors often stem from mismatched parentheses, missing arguments or incorrect data types.

What Is the IF Formula in Excel?

The IF function in Excel is one of the most widely used logical functions in any spreadsheet. Many people search for the "IF/THEN formula," but in Excel the function is simply called IF. It lets you test a condition and return one value when the condition is true and a different value when it is false.

The basic syntax looks like this:

=IF(logical_test, value_if_true, [value_if_false])

Each argument plays a specific role:

• logical_test: The condition you want to evaluate, such as A1>100 or B2="Yes."

• value_if_true: The result the formula returns when the logical test is met. This can be a number, text string, cell reference or another formula.

• value_if_false: The result the formula returns when the logical test is not met. This argument is optional, but leaving it out returns FALSE by default.

This guide covers basic IF formulas, nested IFs for multiple conditions and combining IF with AND or OR. If you are brand new to the IF function, our introductory post covers the fundamentals of applying discounts to customer quotes based on a set of criteria being met. The examples below build on that foundation, but you can follow along even if you are starting here.

How to Write a Basic IF Formula

Before diving into more advanced scenarios, start with a straightforward example. Suppose you have a list of sales totals in column B and you want to flag every sale that meets or exceeds a $500 target.

=IF(B2>=500, "Met Target", "Below Target")

Here is how each part works:

• B2>=500 is the logical test. It checks whether the value in cell B2 is 500 or greater.
• "Met Target" is the value returned when the condition is true.
• "Below Target" is the value returned when the condition is false.

Copy this formula down the column and Excel evaluates each row independently. You get an instant, at-a-glance view of which sales hit the mark and which fell short—and you can pair this with conditional formatting to add color-coded visual cues to your results. Once you are comfortable with this pattern, you are ready to layer in additional conditions using nested IF formulas.

How to Nest IF Formulas for Multiple Conditions

As amazing as the IF formula is alone, it really comes into its own when used in groups. This section introduces you to nested IF functions. Nested IF formulas help you calculate more complex discounts, such as multiple rates for multiple tiers of purchasing.

Problem:

IF the number of units is over 1,000, check to see IF the number is over 5,000. If it is, give the customer a 6% discount. Otherwise, give the customer a 3% discount. If the number of units is not over 1,000, the customer receives no discount.

=IF(D2>1000, IF(D2>5000, 0.06, 0.03), 0)

• D2 is the cell that shows the number of units sold.
• If D2 is greater than 1,000, the formula checks to see if D2 is also greater than 5,000.
• 0.06 is the discount (6%) received if D2 is greater than 5,000. Otherwise, 0.03 is the discount (3%) received for D2 that exceeds 1,000.
• 0 is the discount received if D2 is not greater than 1,000.

How to Use IF with AND or OR for Multiple Conditions

Nested IF formulas work well for tiered logic, but sometimes you need to test several criteria at the same time. That is where the AND and OR functions come in. Wrapping them inside an IF formula lets you evaluate multiple conditions in a single logical test.

Using IF with AND

The AND function requires every condition to be true before the IF formula returns the "true" result. For example, suppose you want to offer a 10% discount only when a customer orders more than 1,000 units and holds a "Gold" membership level:

=IF(AND(B2>1000, C2="Gold"), 0.10, 0)

• B2>1000 checks the unit count.
• C2="Gold" checks the membership level.
• Both conditions must be true for the formula to return 0.10. If either one is false, the result is 0.

Using IF with OR

The OR function returns true when at least one condition is met. For instance, you might offer an 8% discount to any customer who either orders more than 5,000 units or has "VIP" status:

=IF(OR(B2>5000, C2="VIP"), 0.08, 0)

• The formula returns 0.08 if B2 is greater than 5,000, if C2 equals "VIP" or if both are true.
• The result is 0 only when neither condition is met.

You can also combine AND and OR in the same formula for more complex scenarios. Just be sure to close every parenthesis, because mismatched parentheses are the most common source of errors in these formulas.

Common IF Formula Errors and How to Fix Them

Even experienced Excel users run into issues with IF formulas. Here are the most frequent problems and how to resolve them:

• Mismatched or missing parentheses: Every opening parenthesis needs a closing one. In nested formulas, count your parentheses carefully. Excel highlights matching pairs when you click inside the formula bar.
• #VALUE! errors from mixed data types: If your logical test compares a number to a cell that contains text (or vice versa), Excel may return #VALUE!. Make sure the data type in the cell matches what your formula expects.
• Forgetting quotation marks around text values: Text strings in an IF formula must be enclosed in double quotation marks. Writing =IF(A1=Yes, "Pass", "Fail") without quotes around Yes will cause an error. The correct version is =IF(A1="Yes", "Pass", "Fail").
• Exceeding the nesting limit: Excel supports up to 64 levels of nesting, but formulas with more than a few nested IFs become difficult to read and maintain. If you find yourself nesting seven or more conditions, consider switching to the IFS function for cleaner syntax.
• Unexpected results from blank cells: A blank cell is treated as zero in numeric comparisons and as an empty string ("") in text comparisons. If your data has blanks, add a check for them to avoid misleading results.

Build Your Excel Skills with Pryor Learning

The IF formula gives you a powerful tool for adding true/false logic to your data—from discount tiers to finance formulas—helping speed decision-making and save you time on otherwise repetitive tasks.

Once you are comfortable with IF, explore related functions that extend the same logic across larger data sets. IFS lets you evaluate multiple conditions without nesting. SUMIF and COUNTIF apply conditional logic to sums and counts. SUMIFS handles multiple criteria in a single formula. Each of these builds on the foundation you have practiced here and supports data-driven decision making across your organization.

Pryor Learning offers hands-on Excel training courses designed for every skill level, from spreadsheet basics to advanced formulas and data analysis. With a PryorPlus membership, you get unlimited access to live seminars, On-Demand courses and downloadable resources so you can keep sharpening your skills at your own pace.