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.
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.
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.
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.
Practice this function
Nested IF for grading
IntermediateAssign letter grades (A, B, C, D, F) based on numerical scores.
Nested IF for commission tiers
IntermediateCalculate sales commission using a 3-tier rate structure.