The IF function is one of the most used formulas in Excel, and for good reason. It lets a cell display different values depending on whether a condition is true or false — for example, showing “Pass” or “Fail” based on a test score, flagging overdue invoices, or applying different pricing based on order size. Once you understand the basic structure, you can combine IF with AND and OR to test multiple conditions at once, or use the IFS function for a cleaner way to handle several possible outcomes. This guide covers all of it, with practical examples throughout.
The Basic IF Syntax
The IF formula has three parts:
=IF(logical_test, value_if_true, value_if_false)
- logical_test — a condition that evaluates to either TRUE or FALSE (e.g.
A2>50) - value_if_true — what to return if the condition is true
- value_if_false — what to return if the condition is false
Simple Example: Pass or Fail
If scores are in column A and you want column B to show “Pass” or “Fail” depending on whether the score is 50 or above:
=IF(A2>=50, "Pass", "Fail")
If A2 contains 65, the formula returns “Pass”. If it contains 42, it returns “Fail”.
Text vs Number Results
When your result is text, it must be wrapped in double quotation marks. When it’s a number, you don’t use quotes.
Text result: =IF(A2>100, "Over budget", "Within budget")
Number result: =IF(A2>100, 0, A2)
A common mistake is forgetting the quotes around text results, which causes a #NAME? error. If you want the cell to appear blank when the condition is false, use two double quotes with nothing between them: =IF(A2>0, A2, "")
Comparison Operators
Your logical_test can use any of these comparison operators:
=— equal to<>— not equal to>— greater than<— less than>=— greater than or equal to<=— less than or equal to
Nested IF for Multiple Conditions
Sometimes you need more than two possible outcomes. You can nest IF formulas inside each other to test multiple conditions in sequence. For example, assigning a grade based on score:
=IF(A2>=70, "Distinction", IF(A2>=50, "Pass", "Fail"))
This reads: if the score is 70 or above, return “Distinction”; if not, check whether it’s 50 or above and return “Pass”; if neither condition is true, return “Fail”.
You can keep nesting further, but it gets difficult to read quickly. Excel 2019 and later offer the IFS function as a cleaner alternative (covered below). In older versions, you’re limited to 7 levels of nesting, though in practice more than 3 or 4 becomes very hard to maintain.
Using AND Inside IF
The AND function lets you require that two or more conditions are all true at the same time. The syntax is AND(condition1, condition2, ...) and it returns TRUE only if every condition is met.
Example: flag a row only if the order is both overdue AND over £500:
=IF(AND(B2="Overdue", C2>500), "Chase immediately", "OK")
If B2 is “Overdue” and C2 is 750, the result is “Chase immediately”. If either condition is false, the result is “OK”.
Using OR Inside IF
The OR function returns TRUE if at least one condition is met. Use it when any one of several conditions should trigger the result.
Example: flag a product if it’s either out of stock or discontinued:
=IF(OR(B2="Out of stock", B2="Discontinued"), "Review needed", "Active")
You can combine AND and OR inside the same IF formula, though it can get complex. Brackets are important — make sure each function’s arguments are contained correctly.
The IFS Function: A Cleaner Alternative to Nested IF
Available in Excel 2019 and Excel 365, the IFS function lets you test multiple conditions in sequence without nesting. The syntax is:
=IFS(condition1, result1, condition2, result2, condition3, result3, ...)
The grade example from above becomes much easier to read with IFS:
=IFS(A2>=70, "Distinction", A2>=50, "Pass", A2<50, "Fail")
IFS tests each condition in order and returns the result for the first one that is TRUE. There is no default “else” value — if no condition matches, IFS returns #N/A. To avoid this, make your last condition something that is always true, like TRUE, "Fail":
=IFS(A2>=70, "Distinction", A2>=50, "Pass", TRUE, "Fail")
IFS is not available in Excel 2016 or earlier — in those versions you’ll need to use nested IF instead.
Practical Examples
Flag overdue invoices
=IF(TODAY()>C2, "Overdue", "Current")
Where C2 contains the due date. TODAY() returns the current date, so this flag updates automatically each day.
Apply a discount based on quantity
=IF(B2>=100, A2*0.9, IF(B2>=50, A2*0.95, A2))
Where A2 is unit price and B2 is quantity. Orders of 100+ get 10% off, orders of 50–99 get 5% off, smaller orders pay full price.
Show a value or leave blank
=IF(A2="", "", A2*1.2)
Only calculates if A2 has a value — avoids showing zero or an error in rows that haven’t been filled in yet.
Common Errors
Too many nested IFs / mismatched brackets
Each IF adds one opening bracket and needs one closing bracket. Four nested IFs need four closing brackets at the end. If you get a red error about mismatched brackets when pressing Enter, count your opening and closing brackets — they must be equal. Excel will sometimes highlight the mismatched bracket in red to help.
Text without quotes
Any text value in a formula must be in double quotes. Writing =IF(A2>=50, Pass, Fail) without quotes will produce a #NAME? error because Excel looks for named ranges or functions called Pass and Fail.
Condition that returns text instead of TRUE/FALSE
The logical_test must be a comparison, not just a cell reference. =IF(A2, "Yes", "No") will work in some cases (zero = FALSE, anything else = TRUE) but it’s better to be explicit: =IF(A2<>"", "Yes", "No").
The IF function is straightforward once you’ve practised it a few times. Start with simple pass/fail examples and build up to nested conditions and AND/OR combinations as you gain confidence.
