IF()
Returns one value if a condition is true and another if it's false.
IF(logical_test, value_if_true, [value_if_false])IF is the basic branching function: test a condition, return one thing if it's true, another if it's false. Nesting several IFs to handle more than two outcomes gets unreadable fast — IFS is usually clearer.
value_if_false is technically optional; omitting it returns FALSE when the condition doesn't hold, which is rarely what you want.
Most IF problems are structural rather than syntactic. A chain more than three levels deep is usually a lookup table in disguise, and converting it to XLOOKUP against a visible two-column range makes the rule readable and editable by someone who does not write formulas. Where branching genuinely is the right tool, IFS or SWITCH(TRUE, …) express the same logic without the nesting. And guard the blank case first: a test like B2>100 treats an empty cell as zero, so empty rows silently take the false branch rather than staying empty.
Arguments
- logical_test
- Any condition or expression that evaluates to TRUE or FALSE.
- value_if_true
- What to return when logical_test is TRUE.
- value_if_falseoptional
- What to return when logical_test is FALSE. Defaults to FALSE if omitted.
Examples

=IF(B2>=60, "Pass", "Fail")Returns Pass when B2 is 60 or more.
=IF(C2="", "Missing", C2)A common pattern for flagging blank cells.
=IF(B2="", "", IF(B2>Target, "Over", "Under"))The outer IF guards the blank case first, so empty rows stay empty instead of being reported as Under.
Notes and gotchas
- The third argument is optional, and omitting it returns
FALSErather than a blank — which then sums as 0 and displays the word FALSE. Supply""explicitly when you want nothing. IF(A1="",...)is true for both an empty cell and a cell containing a zero-length string from a formula.ISBLANKdistinguishes them;IFdoes not.- Text comparison is case-insensitive, so
IF(A1="yes",...)matchesYESandYes. UseEXACTwhen case matters. - Both branches are evaluated lazily, so an expensive calculation in the unused branch costs nothing — unlike
CHOOSE, which evaluates everything. - Nesting more than three or four levels deep is the point to stop. Use
IFS,SWITCH, or a lookup table instead — a nested IF chain is where threshold bugs hide.
Common errors
| You see | Because | Fix |
|---|---|---|
| #VALUE! | A branch does arithmetic on text, or the condition itself returns an error from a nested formula. | Test the condition alone in a spare cell first — the failure is usually there, not in IF. |
| Returns FALSE unexpectedly | The value_if_false argument was omitted. | Add a third argument, even if it is just "". |
IF tricks, tips, and workbook hacks
IF is the branching switch of spreadsheet logic: test one condition, return one result when TRUE, and another when FALSE. While a single IF is easy to read, workbooks quickly become fragile when authors stack five or six nested IFs together, quote numbers as text ("100" instead of 100), or forget to handle blank input cells before testing a threshold.
Before writing a deep IF chain, ask whether your logic is really a lookup table (better served by XLOOKUP with match_mode -1) or a clean multi-branch rule (better served by IFS or SWITCH).
Quick IF checks before trusting the result
- Never wrap numeric outputs in quotation marks—"0" or "100" turns your result into text that SUM() will silently ignore.
- Guard against empty cells first: =IF(A2="", "", IF(A2>=70, "Pass", "Fail")) keeps blank rows from being labeled as failures.
- Replace 4+ levels of nested IF thresholds with a 2-column reference table and XLOOKUP(..., -1) so business users can see and edit the cutoffs on the grid.
- Combine multiple conditions inside the 1st argument using AND(...) or OR(...) instead of nesting separate IFs.
| IF pattern | IF formula | Best use | Watch out |
|---|---|---|---|
| Suppress calculations until input data is entered | =IF(ISBLANK(B2),"",B2*C2) | Invoice templates and order forms where empty rows should stay visually blank instead of showing $0.00. | If B2 contains a formula that returns "", use B2="" instead of ISBLANK(B2). |
| Compound condition with AND / OR | =IF(AND(B2>=5000,C2="Approved"),B2*0.08,0) | Commission rules that require both a minimum deal size and an approved status. | Return numeric 0 (not "0") so the commission column sums cleanly. |
| Budget variance status flag | =IF(ABS(C2-B2)/B2>0.1,"Investigate","On Track") | Flagging any expense line that deviates by more than 10% in either direction from budget. | Wrap in IF(B2=0, "New Line", ...) if budgeted amount B2 can be zero. |
| Dynamic range switch inside an aggregation | =SUM(IF(G1="YTD",B2:M2,B2:D2)) | Switching a summary total between Q1 (B2:D2) and Full Year (B2:M2) based on a toggle cell G1. | IF returns an actual cell range reference when both branches are ranges. |
| IF mistake | What happens in IF | Fix |
|---|---|---|
| Quoting numbers inside value_if_true / value_if_false | The column displays numbers on screen (often left-aligned), but SUM() at the bottom skips them. | Remove quotes around numbers and formulas; only text labels belong in quotes. |
| Testing thresholds in the wrong order in nested IFs | =IF(A2>50,"Low",IF(A2>90,"High","None")) labels 95 as "Low" because the first test (>50) already succeeded. | Always order sequential numeric tests from highest to lowest (or lowest to highest when using <). |
| Omitting value_if_false | Rows that fail the condition display the literal word FALSE right in the middle of a client report. | Always provide the 3rd argument—use "" or 0 if nothing should display. |
IF practice drills
- Write an overdue-invoice flag that checks three cases: PaidDate is populated ("Paid"), DueDate < TODAY() ("Overdue"), otherwise "Current".
- Test the difference between =SUM(D2:D10) when IF returns "50" (quoted) versus 50 (unquoted).
- Refactor a 4-level nested IF grading formula into an IFS() formula and an XLOOKUP(..., -1) table lookup.



