IF

Returns one value when a condition is true and another when it's false

Logical

|

Excel All versions

|

Google Sheets Supported

Spreadsheet editor

Spreadsheet editor

Syntax

=IF(Logical_test, Value_if_true, Value_if_false) Returns: Varies

Arguments

Argument	Required	Description
Logical_test	Yes	The condition that you want to test. It can be an expression, a value, or a cell reference.
Value_if_true	Yes	The value or formula to return if the logical test evaluates to TRUE.
Value_if_false	No	The value or formula to return if the logical test evaluates to FALSE.

About

IF allows you to test any condition and get different results based on whether it's true or false. Use it to categorize data, flag important values, or run different calculations depending on your criteria.

This function works for countless scenarios: marking students as pass or fail, applying discounts to sales over a threshold, or displaying custom messages based on inventory levels. Combine IF with functions like AND and OR to test multiple conditions at once, or use IFS when you need to check several conditions in sequence.

IF is available in all Excel versions and forms the foundation for more advanced logical operations.

Exercises using IF

IF with numeric output

Calculate a 10% bonus if sales exceed target, otherwise 0.

Simple IF statement

Categorize sales as "On target" or "Below target" based on a threshold.

Creating an IFNOTERROR function

Flag overdue invoices with IF and NOT

Use IF combined with NOT to flag overdue invoices that are not marked as paid.

Practicing and, or, and if

Aging buckets

Categorize receivables into aging buckets (Current, Slightly overdue, Moderately overdue, Seriously overdue).

Nested IF for grading

Assign letter grades (A, B, C, D, F) based on numerical scores.

IF with OR

Flag high-priority orders (either > $10,000 OR customer is "VIP").

IF with AND

Approve a loan only if credit score > 700 AND income > $50,000.

Nested IF with AND/OR for eligibility

Determine training eligibility using IF with nested AND and OR tests.

Nested IF for commission tiers

Calculate sales commission using a 3-tier rate structure.

Remove duplicate customer records

Use COUNTIF with an expanding range to mark which customer records to keep and which repeats to remove.

Build a one-variable sensitivity table

Build a project NPV model and test whether it clears a required threshold under different discount rates.

A/B test results analysis

Compare two experiment variants by calculating conversion rates, percentage lift, standard errors, and a significance decision.

Bank statement reconciliation

Reconcile a bank statement with internal records, identify missing items.

Complex commission structure

Calculate commissions with tiered rates, team bonuses, and caps.

Full data reconciliation

Match transactions between two systems, flag discrepancies by type.

Pareto analysis (80/20)

Build a complete Pareto analysis ranking defect categories by frequency with cumulative percentages and 80/20 classification.

Show 17 more Show 16 more Show 15 more

Examples

Empty cell handling

See how IF treats empty cells as zero in this attendance tracker. Liam's empty attendance cell evaluates to FALSE, showing 'Needs Improvement' even though no data was entered. Try entering a number in B3 to watch the status change.

Spreadsheet editor

Missing third argument mistake

Watch what happens when you leave out the third argument. Column C shows FALSE for employees who don't qualify, while Column D correctly shows blank cells. Change any score to 80 and compare the results.

Spreadsheet editor

Nested IF with AND function

Use IF with AND to check multiple conditions for bonus eligibility. Employees need both 2+ years of service and $50,000+ in sales. Edit Tom's years or Lisa's sales to see when they become eligible.

Spreadsheet editor

Watch out for

⚠

Missing value_if_false causes confusion

Leaving out the third argument returns FALSE instead of a blank when the condition isn't met, which can mess up calculations or formatting.

→ Always include the third argument. Use empty quotes ("") if you want a blank result when the condition is false.

⚠

Text comparisons are case-insensitive

IF treats "apple" and "APPLE" as the same value, which might not match your intent when comparing text.

→ Use EXACT within your IF statement when you need case-sensitive text comparisons.

⚠

Deeply nested IFs become unreadable

Nesting more than 3-4 IF statements makes formulas hard to understand, debug, and maintain.

→ Switch to IFS for cleaner multi-condition logic, or use SWITCH when comparing a single value against multiple options.

⚠

Logical tests with errors break the formula

If your logical test references cells with errors (like #DIV/0!), the entire IF formula returns an error.

→ Wrap error-prone conditions with IFERROR or ISERROR to handle errors gracefully before they reach IF.

Tips & notes

Your logical test can include comparison operators (=, <, >, <=, >=, <>), cell references, functions, or combinations. You can return numbers, text, dates, or even other formulas in the value_if_true and value_if_false arguments. When nesting IF statements, work from the inside out to keep track of your logic.

Common questions

Can I use formulas in the value_if_true or value_if_false arguments?

Yes. Both arguments accept formulas, which means you can perform calculations, call other functions, or even nest additional IF statements. This gives you flexibility to create dynamic results based on your condition.

What's the maximum number of IF functions I can nest?

Excel 2007 and later support up to 64 nested IF functions, but most people find more than 3-4 hard to manage. Consider using IFS or lookup functions for complex multi-condition scenarios instead.

How do I check multiple conditions in one IF statement?

Combine IF with AND to require all conditions to be true, or with OR to accept any condition being true. For example: =IF(AND(A1>50, B1<100), 'Yes', 'No') checks both conditions.

Why does my IF formula return TRUE or FALSE instead of my values?

You likely forgot to include the value_if_true and value_if_false arguments. IF needs all three parts: the test, what to return when true, and what to return when false.