IF
Every question on this hub is the same function with a different comparison inside it; what changes is how many conditions must be weighed at once. One condition with two outcomes needs nothing else. Several that must all hold take AND inside the test, any-one-of-several takes OR, and a run of ordered bands wants nesting — or IFS, where the version allows it. Checking whether a cell contains text rather than equals it needs a helper, because neither Excel nor Google Sheets has a contains operator at all. A test that can error out has to be wrapped before it is asked. Choose below by the shape of the condition, not by the function name.
The formula
=IF(B2>=60,"Pass","Fail")one condition, two outcomes=IF(ISBLANK(B2),"Missing",B2)test for a blank cell=IF(ISNUMBER(SEARCH("urgent",B2)),"Flag","")test if a cell contains specific text=IF(AND(B2>=60,C2="Submitted"),"Pass","Review")every condition must be true=IFERROR(IF(B2>=60,"Pass","Fail"),"Check input") fall back to a value instead of an errorA worked example
B2 holds a test score of 72, and the pass mark is 60.
=IF(B2>=60,"Pass","Fail")
"Pass" — text, not a number, so it left-aligns in the cell. The quotes around Pass and Fail are what make them literal text instead of formula errors; drop them and Excel looks for cells or names called Pass and Fail.
Which one do I need?
| If you want to… | Use |
|---|---|
| One condition, exactly two outcomes | =IF(condition, value_if_true, value_if_false) |
| A few ordered conditions where each check only matters once the earlier ones fail (grade bands, tiered pricing) | Nested IF: =IF(cond1,res1,IF(cond2,res2,res3)) — modern Excel allows up to 64 levels, but readability breaks down long before that |
| Many ordered conditions and nesting is getting hard to read | =IFS(cond1,res1,cond2,res2,...,TRUE,fallback) — Excel 2019, 2021 and 365 only (not 2016 or earlier); current Google Sheets has it too. Returns #N/A if nothing matches and there is no final TRUE fallback |
| Every one of several conditions must be true at once | =IF(AND(cond1,cond2), true_result, false_result) |
| Any one of several conditions is enough on its own | =IF(OR(cond1,cond2), true_result, false_result) |
| The condition itself might error out (a lookup, a division) before IF can even test it | Wrap the whole thing: =IFERROR(IF(...), "fallback") |
Frequently asked questions
Why does my IF formula return FALSE instead of a blank cell?
value_if_false was left out. IF then returns the boolean FALSE for a failed test rather than an empty string. Supply it explicitly for a blank-looking result: =IF(B2>=60,"Pass","") shows nothing instead of FALSE when B2 is under 60.
How do I test if a cell contains specific text, not just equals it?
=B2="urgent" only matches a cell that holds exactly that word and nothing else. To match text anywhere inside the cell, wrap SEARCH in ISNUMBER: =IF(ISNUMBER(SEARCH("urgent",B2)),"Flag","") returns "Flag" for any cell containing "urgent", case-insensitive.
Does IF work the same in Google Sheets?
Yes — IF(logical_expression, value_if_true, value_if_false) takes the same three arguments in the same order and behaves identically. IFS is also available in current Google Sheets, without the version gating Excel has (2019/2021/365 only).
Why does IFS return a #N/A error instead of a result?
IFS checks its conditions in order and stops at the first TRUE one; if none of them are true, it has nothing to return and shows #N/A. Add a final catch-all pair — IFS(cond1,res1,cond2,res2,TRUE,"fallback") — so there's always a match.
New guides and tools, once a month
DE + EN · double opt-in · no spam