Most spreadsheet headaches come back to the same family of formulas, and the IF function sits right in the middle of them. You already know the basic shape: =IF(test, value_if_true, value_if_false). Fine. But the moment your test has two conditions, or you need to ignore blank cells, or a #N/A error breaks your summary row, the simple form starts to fall apart. That is where examples earn their keep.
This guide skips the introductory syntax (the sibling article how to use the IF function in Excel covers that) and focuses on the example patterns people search for again and again. We will look at IF combined with AND, OR, ISBLANK, ISNUMBER+SEARCH, IFERROR, AVERAGE and AVERAGEIF, plus nested IF chains and the newer IFS function for else-if logic.
Every formula here is copy-paste safe. You can drop it into Excel 365, Excel 2019, Excel 2021, or Excel for the web with only minor tweaks. Where a function is version-specific (IFS, for instance), the article calls it out. Read top-to-bottom or jump to the section that matches the error you are looking at right now.
One thing before we dig in. The order of the examples follows how questions actually come up in real spreadsheets: multi-condition checks first, because they are the most-asked. Empty-cell handling next, because it is the most-skipped (and the most-debugged). Partial text matching, error trapping, and conditional averages after that. Each example includes the version of Excel it works in and the most common breakage mode, so you can short-circuit the debug cycle if your formula misbehaves.
The first pattern people ask about: how do I check that two things are true before returning a result? The answer is AND() nested inside the IF test. AND returns TRUE only if every argument evaluates to TRUE, which is exactly what you want for a strict gate.
Say column B holds a score out of 100 and column C holds attendance percent. A student passes only if score is at least 70 AND attendance is at least 80:
=IF(AND(B2>=70, C2>=80), "Pass", "Retake")Three things to notice. First, AND can take up to 255 arguments — you are not limited to two checks. Second, AND short-circuits internally, so order does not affect the result (unlike some programming languages). Third, blank cells inside AND are treated as FALSE, which sometimes bites people. If B2 is empty, the AND collapses and you get "Retake," not an error.
Need three conditions? Just keep adding:
=IF(AND(B2>=70, C2>=80, D2="Submitted"), "Pass", "Retake")It reads almost like English, and that readability is why AND is preferred over multiplying boolean expressions together — even though =(B2>=70)*(C2>=80) technically works the same way.
If every condition must be true, use AND. Reaching for nested IFs to mimic AND makes the formula longer and harder to audit. A good rule: when you find yourself writing IF(IF(IF(, stop and ask whether AND or IFS would do the job in one line.
OR is the looser sibling. It returns TRUE if at least one argument is TRUE. Useful when several different inputs should fire the same outcome. Flagging risky orders is a classic case — maybe an order is risky if the amount exceeds $5,000, OR the country is on a watchlist, OR the customer is brand new:
=IF(OR(B2>5000, C2="HighRisk", D2<TODAY()-30), "Review", "OK")Mixed AND/OR logic is allowed and very common. Pass students who scored 70+ AND either attended 80% of classes OR submitted the makeup assignment:
=IF(AND(B2>=70, OR(C2>=80, D2="Makeup")), "Pass", "Retake")Watch your parentheses here — that is where most "Excel says my formula is broken" tickets come from. A trick: write the inner condition on its own first in a free cell, confirm the boolean output is TRUE or FALSE as expected, then copy it into the IF wrapper. Building from the inside out beats trying to debug a 90-character formula in one shot.
One more thing worth noting about OR. It is the natural choice for "any of these flags" checks, but it can also model an exclusion list. If you want to skip rows where status is "Closed", "Cancelled", or "Refunded":
=IF(OR(A2="Closed", A2="Cancelled", A2="Refunded"), "", B2*C2)For longer exclusion lists, you can replace the chained OR with a single MATCH against a range — cleaner and easier to maintain when the list grows:
=IF(ISNUMBER(MATCH(A2, ExcludeList, 0)), "", B2*C2)All conditions must be TRUE. Strict gate. Accepts up to 255 arguments. Treats blanks as FALSE and short-circuits internally so argument order does not affect the result.
Any one condition can be TRUE. Loose match. Up to 255 arguments. Best for risk flags, exclusion lists, and any rule where multiple triggers should produce the same outcome.
Exactly one condition must be TRUE. Rarely needed but handy for toggle states or mutual-exclusion gates where one and only one input should fire.
Inverts a boolean result. Wrap around AND or OR to invert the whole gate. Often clearer than rewriting the condition with the opposite comparison.
Else-if chain — cleaner than nested IFs for multi-bucket rules. Excel 2019, 2021, 365 and Excel for the web. Always include a final TRUE catch-all to avoid #N/A.
Match a single value against a list of cases. Reads like a lookup. Good for grade letters, size codes, status mapping, and short categorical conversions.
Empty cells are tricky in Excel. A cell can look empty but actually contain a zero-length string returned by another formula. ISBLANK only catches the truly empty case. That distinction matters when you write summary formulas.
=IF(ISBLANK(A2), "Missing", A2)Returns "Missing" only when A2 is genuinely empty. If A2 holds ="", ISBLANK returns FALSE and you will get the empty string instead.
=IF(A2="", "Missing", A2)This one treats both truly empty cells and empty strings as missing. Most of the time, that is what you actually want, because formula-driven cells frequently produce "" as a "no value" output.
=IF(NOT(ISBLANK(A2)), A2*1.1, "")Adds 10% to A2 only when A2 has a real value, otherwise leaves the result blank. Substitute A2<>"" for the looser check.
For a row of inputs where all must be filled before calculating:
=IF(COUNTA(A2:C2)=3, A2+B2+C2, "Fill all fields")COUNTA counts non-empty cells (including formula-driven empty strings as filled). Useful when you want to be permissive — anything other than a truly empty cell counts.
Returns TRUE only for truly empty cells. Use this when you need to distinguish a never-touched cell from a formula that returned an empty string. ISBLANK is the strict option — it ignores zero-length strings produced by other formulas, so a cell holding ="" reads as not blank even though it looks empty.
Tests with =A2="". Returns TRUE for both genuinely empty cells AND for cells containing the empty string. This is the forgiving option and usually the right pick for everyday work, especially when your source column is populated by formulas that may return "" as a no-value placeholder.
Same outcome as the equals-blank test but more explicit. Some auditors prefer LEN(A2)=0 because the intent is unambiguous and it removes any confusion about whether you are testing for blank or for a specific string. Identical performance to ="" in modern Excel.
Range-friendly. Use COUNTA when you want to confirm a whole block of cells is fully populated before running a calculation. =IF(COUNTA(A2:C2)=3, sum, "") gates the calc on all three inputs being filled. Treats formula-driven empty strings as populated.
One of the most common "I cannot make this work" questions: how do I check if a cell contains a substring, not equals it exactly? The native combo is ISNUMBER + SEARCH. SEARCH returns the position of the substring if found, or #VALUE! if not. ISNUMBER catches the position case as TRUE and the error as FALSE.
=IF(ISNUMBER(SEARCH("invoice", A2)), "Has invoice", "No")SEARCH is case-insensitive. If you need case-sensitive, swap SEARCH for FIND. Same wrapping logic.
Use OR to widen the test:
=IF(OR(ISNUMBER(SEARCH("urgent", A2)), ISNUMBER(SEARCH("priority", A2))), "Flag", "")Or, more elegantly with SUMPRODUCT for a list of terms in a separate range:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(D2:D5, A2)))>0, "Flag", "")That second formula lets you maintain the keyword list in D2:D5 without touching the formula again.
Swap OR for AND and adjust the threshold:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(D2:D5, A2)))=COUNTA(D2:D5), "All present", "")When the answer depends on more than two outcomes, you have two paths: nested IFs or the newer IFS function. Both work. IFS is shorter and easier to read.
Grade letter from a numeric score:
=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))Read it from the outside in. The first condition that matches wins; the rest never run. Modern Excel supports up to 64 levels of nesting, but anything past four or five gets painful to maintain.
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F")Pairs of test/result, evaluated top to bottom. The final TRUE, "F" acts as the catch-all default — without it, scores below 60 would return #N/A.
If you are matching a value to a list (not a range), SWITCH reads even cleaner:
=SWITCH(B2, "S", "Small", "M", "Medium", "L", "Large", "Other")Errors in Excel propagate. One #N/A in column D ruins your SUM in row 200. IFERROR is the cleanest fix.
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not found")If the VLOOKUP fails, you get "Not found" instead of #N/A. Same idea with IF:
=IFERROR(IF(B2/C2>1, "Over", "Under"), 0)If C2 is zero, the division throws #DIV/0! and IFERROR returns 0. Use IFNA if you only want to trap #N/A and let other errors surface — useful during debugging.
=IF(A2="", "", IFERROR(VLOOKUP(A2, table, 2, FALSE), "New entry"))Three layers of safety: blank input returns blank, missing lookup returns a friendly label, real match returns the value. This is one of the most copy-pasted Excel patterns on the planet.
If you want to trap only the "not found" case but let #DIV/0! or #VALUE! show up so you notice them:
=IFNA(VLOOKUP(A2, table, 2, FALSE), "Not in list")Sometimes the value you return from an IF is not a label — it is a calculation, often an average over a filtered range. Two patterns cover most cases.
If a region's name is "North," show the average of column C for North; otherwise blank:
=IF(A2="North", AVERAGEIF(A:A, "North", C:C), "")AVERAGEIF does the heavy lifting. The IF just gates whether you show it.
=AVERAGEIFS(C:C, A:A, "North", B:B, ">100")Average of column C where column A equals "North" and column B is greater than 100. No IF wrapper needed — AVERAGEIFS handles the conditions itself.
=IF(D2="Region", AVERAGEIF(A:A, A2, C:C), AVERAGE(C:C))Switches between a filtered average and the overall average based on a toggle cell. Handy in dashboard cells where the user can pick the scope.
AVERAGE includes zero values; if you want them excluded:
=AVERAGEIF(C:C, "<>0")Or, for a hand-rolled version using IF as an array:
=AVERAGE(IF(C2:C100<>0, C2:C100))In Excel 365 this works as-is; in older versions, confirm with Ctrl+Shift+Enter to make it an array formula.
Let us stack several of these patterns. A sales report with this rule: pay a 5% bonus if quarterly revenue exceeds $10,000 AND error rate is below 2%, except if the territory is on the watchlist, in which case freeze the bonus. Show "Pending" if any input is blank:
=IF(OR(ISBLANK(A2), ISBLANK(B2), ISBLANK(C2)), "Pending",
IF(C2="Watchlist", 0,
IF(AND(A2>10000, B2<0.02), A2*0.05, 0)))Three layers — completeness check, watchlist override, then the AND test for the actual bonus. Wrap the whole thing in IFERROR if any of those inputs could be a formula that occasionally errors out.
A few patterns trip up everyone at some point. Worth scanning the list before debugging blind.
$A$1 stays put while A1 drifts. If your IF compares against a single lookup cell, lock it."70" is text, 70 is a number. IF(B2>="70", ...) compares text and returns surprising results.TRUE, default, unmatched inputs return #N/A.>TODAY() never matches. Convert with DATEVALUE first.IF(B2>0, TRUE, "no") mixes a boolean and a string in the same column, which messes up downstream filters.None of these are difficult once you have seen them; they are just the same handful of footguns every Excel user steps on at least once.
The formulas above scale to a few hundred rows without thinking about it. Past that, two things change. First, you start to feel the recalculation cost — every IF re-evaluates whenever any referenced cell changes. Second, copy-paste mistakes multiply, because a small wrong reference in row 2 propagates 50,000 times.
A few habits that keep large IF columns sane. Convert the data range to an Excel Table (Ctrl+T). Tables auto-fill formulas down and use structured references like [@Revenue] instead of B2, which makes the formula self-documenting and immune to insert/delete row shifts. If your IF compares against a config cell, name the cell (Formulas tab, Define Name) so the formula reads IF(B2>ThresholdRevenue, ...) instead of IF(B2>$G$1, ...). Future-you will thank present-you.
For really large sheets — 100,000+ rows — consider whether the IF needs to recalculate constantly. If the underlying data only changes when you import a new CSV, switch the workbook to Manual calculation mode (Formulas tab, Calculation Options). Excel will recalculate when you press F9, not on every keystroke.
IF is a hammer. Most things look like nails. But a few situations call for a different tool entirely, and reaching for the right one upfront saves debugging hours later.
If you are counting how many cells meet a condition, COUNTIF or COUNTIFS replaces a long IF + SUM combo. Want to sum only matching values? SUMIF or SUMIFS. Looking up a value from a table? VLOOKUP, XLOOKUP, or INDEX/MATCH — these read cleaner than nested IFs for lookups. Picking the Nth largest value with conditions? Use MAXIFS or MINIFS. Building a pivot-style summary across categories? An actual PivotTable usually beats a forest of IF formulas.
The general rule: if your IF formula is over 80 characters or contains more than three nested IFs, ask whether a dedicated function (or IFS, or SWITCH) would do the same job in fewer keystrokes. Shorter formulas are almost always easier to debug six months from now when you have forgotten exactly what you were trying to compute.