Excel IF Function: Complete Guide with Examples

The IF function makes decisions in Excel. Test conditions and return different values based on whether the condition is TRUE or FALSE. Essential for automated calculations and logic.

Basic IF Syntax

IF takes three arguments: a test that is TRUE or FALSE, the value to return when it's TRUE, and the value to return when it's FALSE. For example, =IF(A2>100000,"High","Low") returns High when A2 is 150000, because 150000 > 100000 is TRUE; it returns Low for 100000 or less. If you leave out value_if_false entirely, IF returns FALSE when the test fails; if you keep the comma but leave the argument empty, it returns 0.

Simple IF Statement

=IF(logical_test, value_if_true, value_if_false)

Example:
=IF(A2 > 100, "High", "Low")

If A2 is greater than 100, show "High"
Otherwise, show "Low"

Common Comparison Operators

=      Equal to                =IF(A2 = "Active", "Yes", "No")
>      Greater than            =IF(A2 > 1000, "Bonus", "")
<      Less than               =IF(A2 < 18, "Minor", "Adult")
>=     Greater than or equal   =IF(A2 >= 65, "Retire", "Work")
<=     Less than or equal      =IF(A2 <= 0, "Error", A2)
<>     Not equal to            =IF(A2 <> "", A2, "Blank")

How do I write an IF formula for less than or more than 70%?

Compare the cell to 70% directly, for example =IF(B2>=70%,"Pass","Fail"). Excel stores percentages as decimals, so 70% is the value 0.7, and typing 70% or 0.7 in the formula gives the same result. The common mistake is comparing a percentage-formatted cell to plain 70: 85% is stored as 0.85, which is not greater than 70, so percentage rows all get the FALSE result.

B2 = score formatted as a percentage (e.g., 85%)

70% or more:          =IF(B2 >= 70%, "Pass", "Fail")
Less than 70%:        =IF(B2 < 70%, "Below target", "On target")
Three outcomes:       =IF(B2 > 70%, "Above 70%", IF(B2 < 70%, "Below 70%", "Exactly 70%"))
Between 60% and 70%:  =IF(AND(B2 >= 60%, B2 <= 70%), "In range", "Out of range")
From two numbers:     =IF(C2/D2 >= 70%, "Met", "Missed")   (C2 = actual, D2 = target)

If B2 holds whole numbers like 85 (no % format), compare to 70 instead:
=IF(B2 >= 70, "Pass", "Fail")

Practical Business Examples

Most everyday IF formulas label a row against a threshold, such as =IF(B2>80,"Good","Bad") or =IF(A2>=50,"Pass","Fail"), or switch between two calculations. Put text results in double quotes; numbers, cell references, and formulas go in without quotes.

Pass/Fail Grading

=IF(B2 >= 60, "Pass", "Fail")

B2 = test score
Pass if 60 or above, otherwise Fail

Sales Commission Calculator

=IF(A2 > 10000, A2 * 0.15, A2 * 0.10)

If sales > $10,000: 15% commission
Otherwise: 10% commission

Discount Pricing

=IF(B2 >= 100, A2 * 0.9, A2)

If quantity >= 100: 10% discount (multiply by 0.9)
Otherwise: regular price (no discount)

Status Based on Date

=IF(C2 < TODAY(), "Overdue", "On Time")

If due date is before today: Overdue
Otherwise: On Time

Nested IF Statements

To handle more than two outcomes, put another IF in the value_if_false argument. Excel checks the tests in order and returns the result of the first one that is TRUE, so when you use >= list the thresholds from highest to lowest.

Multiple Conditions (Grading Scale)

=IF(A2 >= 90, "A",
    IF(A2 >= 80, "B",
        IF(A2 >= 70, "C",
            IF(A2 >= 60, "D", "F"))))

90+: A
80-89: B
70-79: C
60-69: D
Below 60: F

Tiered Commission Structure

=IF(A2 >= 50000, A2 * 0.20,
    IF(A2 >= 25000, A2 * 0.15,
        IF(A2 >= 10000, A2 * 0.10, A2 * 0.05)))

$50K+: 20%
$25K-$49.9K: 15%
$10K-$24.9K: 10%
Under $10K: 5%

Better Alternative: IFS Function (Excel 365)

=IFS(
    A2 >= 90, "A",
    A2 >= 80, "B",
    A2 >= 70, "C",
    A2 >= 60, "D",
    TRUE, "F"
)

Easier to read than nested IFs
Tests conditions in order until one is TRUE

IF with AND/OR Logic

Use AND inside IF when every condition must be TRUE, and OR when any one is enough. Both accept more than two conditions (up to 255), for example =IF(AND(B2>=45,B2<=55,C2<4),"Normal","Check"). For a range check, make sure the lower bound is actually below the upper bound, or AND can never return TRUE.

IF with AND (All Conditions Must Be TRUE)

=IF(AND(B2 >= 18, C2 = "Active"), "Eligible", "Not Eligible")

Both conditions must be TRUE:
- Age >= 18 AND
- Status = "Active"

Use for: Qualification checks, multi-criteria approvals

IF with OR (Any Condition Can Be TRUE)

=IF(OR(B2 = "VIP", C2 > 50000), "Priority", "Standard")

Either condition TRUE:
- Customer is VIP OR
- Lifetime value > $50,000

Use for: Alternative qualification paths

Complex Logic Combinations

=IF(AND(B2 >= 60, OR(C2 = "Active", D2 = "Pending")), "Approve", "Reject")

Score >= 60 AND (Status is Active OR Pending)
All AND conditions + at least one OR condition must be TRUE

Advanced IF Techniques

IF gets more useful when the test comes from another function: a lookup, a type check like ISNUMBER, or a blank-cell check. For lookups, IFNA (Excel 2013 and later) or IFERROR wraps the lookup once instead of repeating it; IFNA catches only #N/A, while IFERROR catches every error type.

IF with VLOOKUP

=IF(ISNA(VLOOKUP(A2, PriceTable, 2, FALSE)), "Not Found", VLOOKUP(A2, PriceTable, 2, FALSE))

Returns price if found, "Not Found" if not
Prevents #N/A errors from displaying

IF with ISNUMBER/ISTEXT

=IF(ISNUMBER(A2), A2 * 1.1, "Invalid Entry")

If A2 is a number: increase by 10%
Otherwise: show error message

Other useful tests: ISBLANK, ISERROR, ISTEXT

Blank Cell Handling

=IF(A2 = "", "Missing", A2)

If cell is blank: show "Missing"
Otherwise: show cell value

Or check for both blank and spaces:
=IF(TRIM(A2) = "", "Missing", A2)

Return Blank Instead of Zero

=IF(A2 = 0, "", A2)

If zero: return empty string (blank cell)
Otherwise: show value

Cleaner looking reports without zeros everywhere

Real-World Dashboard Examples

For status labels, nest IFs from the best tier down so each row lands in exactly one bucket. If the metric is a percentage-formatted cell, compare it to 100% and 70%, not 100 and 70.

Traffic Light Status Indicator

=IF(B2 >= 100%, "🟢 On Track",
    IF(B2 >= 70%, "🟡 At Risk", "🔴 Critical"))

B2 = % of target (e.g., 85%)

100%+: Green - On Track
70-99%: Yellow - At Risk
Below 70%: Red - Critical

Age Category Bucketing

=IF(A2 < 18, "Minor",
    IF(A2 < 35, "Young Adult",
        IF(A2 < 55, "Middle Age", "Senior")))

Perfect for demographic analysis

Inventory Reorder Alert

=IF(C2 <= D2, "REORDER NOW", IF(C2 <= D2*1.5, "Low Stock", "OK"))

C2 = Current stock
D2 = Reorder point

At or below reorder point: REORDER NOW
Within 50% above reorder point: Low Stock
Otherwise: OK

IF with Calculations

The value_if_true and value_if_false arguments can be formulas, so one cell can apply different math to each row. Don't wrap those formulas in quotes, or Excel returns them as text instead of calculating them.

Overtime Pay Calculation

=IF(B2 > 40, (B2 - 40) * C2 * 1.5 + 40 * C2, B2 * C2)

B2 = Hours worked
C2 = Hourly rate

If hours > 40:
  Regular pay (40 hrs) + Overtime pay (1.5x rate)
Otherwise:
  Regular pay only

Conditional Markup

=IF(A2 = "Wholesale", B2 * 1.30, B2 * 1.50)

Wholesale customers: 30% markup
Retail customers: 50% markup

Text Manipulation with IF

Text comparisons in IF are not case-sensitive: =IF(B2="primary",500000,"") matches primary, Primary, and PRIMARY, so wrapping the cell in LOWER() isn't needed. Use EXACT when case matters: =IF(EXACT(B2,"Primary"),500000,"").

Add Prefix Based on Condition

=IF(B2 = "Urgent", "⚠️ "&A2, A2)

If status is Urgent: add warning emoji
Otherwise: show name as-is

Conditional Concatenation

=IF(C2 <> "", A2 & " " & B2 & " " & C2, A2 & " " & B2)

If middle name exists: include it
Otherwise: just first and last name

Common Errors & Solutions

Most IF problems come from mixed data types, missing quotes around text, or nested tests in the wrong order.

Error	Cause & Solution
#VALUE!	Math on a text value (e.g., "N/A" * 2) inside the formula. Clean the data or test it with ISNUMBER() first
#NAME?	Forgot quotes around text: Use "Yes" not Yes
Wrong result	Check the operator (> vs >=), the order of nested IFs, and numbers stored as text (text always compares as greater than any number)
All FALSE	Extra spaces in text. Use TRIM() or exact match

Performance Best Practices

Excel 2007 and later allow up to 64 nested IFs, but formulas past three or four levels are hard to read and easy to break.

• ✅ Limit nested IFs to 3-4 levels max (use IFS or SWITCH instead)
• ✅ Order nested tests so the result is correct first (highest to lowest threshold for >=). Only reorder for speed when the conditions don't overlap.
• ✅ Use comparison operators correctly (>= vs >)
• ✅ Avoid volatile functions in IF when possible (NOW, TODAY, RAND)
• ❌ Don't nest IFs more than 7 levels (Excel's old limit)

Modern Alternatives (Excel 365)

IFS and SWITCH replace most nested IFs. Both are available in Excel 2019 and later and in Microsoft 365.

IFS Function

=IFS(condition1, result1, condition2, result2, TRUE, default)

Cleaner than nested IFs
No need for multiple closing parentheses

SWITCH Function

=SWITCH(A2, "Red", 1, "Blue", 2, "Green", 3, "Other")

Better for exact match scenarios
Replaces multiple IF(A2="value") statements