What is the difference between IF and IFS?

IF handles one condition with an implicit else. IFS handles multiple conditions in one formula. IFS shipped with Excel 2019 - older versions return a NAME error. IFS requires TRUE as the last condition to act as the default.

IF Function in Excel: Syntax, Nested IF, 10 Patterns | Sheets & Cells

Function · Logical

IF — The Foundation of Excel Logic

Every Excel user's first "real" formula. Check a condition, return one value if true, another if false. Simple in principle, endlessly composable in practice — the primitive underneath IFERROR, IFS, SUMIFS, COUNTIFS, and every conditional formatting rule. Universal support since Excel 1.0. Up to 64 levels of nesting.

Quick answer

IF checks a condition and returns one of two values: value_if_true when the condition holds, value_if_false when it doesn't. The logical test can be any expression that evaluates to TRUE or FALSE — comparisons, cell contents, or the result of AND/OR/NOT.

Syntax

=IF(logical_test, value_if_true, [value_if_false])

Working example

=IF(A2>5000, "Big deal", "Standard") → If the amount in A2 is greater than 5000, returns "Big deal"; otherwise returns "Standard". With the workbook data, this tags 10 rows as "Big deal" and 14 as "Standard".

The one gotcha to remember: text outputs must be in quotes. =IF(A2>100, Big, Small) fails because Excel treats Big and Small as named ranges (and returns a NAME error). Correct: =IF(A2>100, "Big", "Small"). Numbers and cell references do NOT need quotes.

📗 Free IF example workbook

9 sheets · same 24-row sales log as the whole IFS family · basic IF · AND/OR · nested tiers · IF vs IFS · IFERROR wrap · 10-pattern cheat sheet · decision tree visualization

Download .xlsx (free) Open in Sheets

Syntax breakdown

IF has three arguments — a logical test and two possible return values. The third is technically optional but almost always specified.

Argument	Type	What it does
`logical_test`	REQUIRED	Any expression that evaluates to TRUE or FALSE. Comparisons (`A2>100`), cell contents (`A2="Yes"`), function results (`ISBLANK(A2)`), or compound conditions (`AND(...)`, `OR(...)`).
`value_if_true`	REQUIRED	What IF returns when the test is TRUE. Can be text (in quotes), a number, a cell reference, or another formula — including another IF for nesting.
`value_if_false`	OPTIONAL	What IF returns when the test is FALSE. If omitted, IF returns FALSE (the literal boolean, which usually looks wrong). Always specify this argument for clarity.

The comparison operators: = (equals), <> (not equals), > (greater than), < (less than), >= (greater or equal), <= (less or equal). Text comparisons are case-insensitive — "North" and "NORTH" match. For case-sensitive matching, use EXACT().

Five working examples

Every example uses the same 24-row sales log as the IFS family. Q1 2026 data: grand total $125,441.65, grand max $13,350, grand min $1,300.

01 Basic IF — pass/fail on a threshold

Tag each transaction as "Big deal" or "Standard" based on a single threshold.

=IF(F2>5000, "Big deal", "Standard")

→ Applied to all 24 rows: 10 Big deals · 14 Standard

Date	Salesperson	Amount	IF Tag
Jan 26	Emma Thompson	$11,570.00	Big deal
Jan 30	David Kim	$9,675.00	Big deal
Feb 3	Sofia Rodriguez	$2,275.00	Standard
Feb 27	Michael Chen	$3,749.85	Standard
Mar 22	Michael Chen	$13,350.00	Big deal
Mar 26	Aisha Patel	$1,300.00	Standard

This is the foundation. Every IF variant on this page builds on the same shape: check something, return one of two things.

02 IF with AND / OR — compound conditions

When you need multiple criteria to jointly hold (or any one to hold), wrap them in AND() or OR() inside the logical test.

Pattern A — AND (all must match)

=IF(AND(C2="North", F2>5000), "Priority", "")

→ Returns "Priority" only if BOTH conditions hold — North region AND above $5,000

Pattern B — OR (any can match)

=IF(OR(C2="North", D2="Gizmo Pro"), "Focus", "")

→ Returns "Focus" if EITHER condition holds — North region OR any Gizmo Pro sale

Pattern C — Nested combination

=IF(OR(AND(C2="North",F2>5000), D2="Gizmo Pro"), "Hot", "Cold")

→ "Hot" if it's a big North deal, OR any Gizmo Pro deal from anywhere. Otherwise "Cold".

Why AND/OR instead of chaining IF? You could write =IF(C2="North", IF(F2>5000, "Priority", ""), ""). But AND reads more naturally, has fewer parens to count, and matches how the requirement is spoken: "North AND above 5000."

03 Nested IF — multi-tier assignment

When you need MORE than two outcomes, IF statements nest inside each other's value_if_false slot.

=IF(F2>=10000, "Platinum", IF(F2>=7000, "Gold", IF(F2>=4000, "Silver", "Bronze")))

→ Assigns a tier to every row. Distribution across 24 deals:

Tier	Threshold	Count	Examples
Platinum	≥ $10,000	3	$11,570 · $10,680 · $13,350
Gold	≥ $7,000	6	$8,900 · $9,675 · $7,120 · $8,600 · $7,525 · $9,250
Silver	≥ $4,000	1	$6,450
Bronze	< $4,000	14	everything else

Order matters: nested IFs are checked left-to-right, top-to-bottom. Excel returns the FIRST match and stops. Always order thresholds from tightest to loosest — from Platinum (highest) down to Bronze (default). Reverse the order and every deal ≥ $4,000 gets Silver, because the check succeeds first.

04 IF vs IFS — the modern replacement

Same tier logic written two ways. IFS() shipped with Excel 2019 as a cleaner alternative to deeply nested IF.

Method 1: Nested IF (works everywhere)

=IF(F2>=10000,"Platinum",IF(F2>=7000,"Gold",IF(F2>=4000,"Silver","Bronze")))

Method 2: IFS (Excel 2019+)

=IFS(F2>=10000,"Platinum", F2>=7000,"Gold", F2>=4000,"Silver", TRUE,"Bronze")

Both produce identical results across all 24 rows. The workbook proves it with a side-by-side comparison and a =IF(E2=F2,"✓","MISMATCH") verification column.

🔗 What that final TRUE in IFS does

IFS has no built-in "else" — every condition needs a match. The trick: put TRUE as the last condition. TRUE always matches, so it becomes the default. Without it, an unmatched row returns the #N/A error. This is IFS's most common bug for new users.

05 IFERROR wrap — the protective clause

Wrap any formula that could break with IFERROR to replace errors with a fallback value.

Division by zero

=IFERROR(D2/C2, "No quota set")

→ If quota (C2) is 0, returns "No quota set" instead of a DIV/0 error

Lookup that might fail

=IFERROR(VLOOKUP(A2, ProductTable, 2, FALSE), "Product not found")

→ Turns N/A errors into readable messages

IFERROR chain (advanced)

=IFERROR(VLOOKUP(A2, T1, 2, FALSE), IFERROR(INDEX(T2, MATCH(A2, K2, 0)), "Not in any table"))

→ Try lookup #1 → if that fails, try lookup #2 → if that fails, text default

When NOT to use IFERROR: IFERROR silently swallows EVERY error, including bugs you'd want to know about. If your formula gets a REF error because you deleted a row, IFERROR quietly returns your fallback instead of surfacing the bug. Use IFERROR for EXPECTED errors (missing lookup, zero denominator), not as a bug swallower.

Interactive playground

Try it Live IF demonstration

Mirrors live cells from the workbook. Edit the yellow input, watch the blue result update.

Input · Amount

8,500

Output · Tier

Gold

=IF(C23>=10000,"Platinum",IF(C23>=7000,"Gold",IF(C23>=4000,"Silver","Bronze")))

Input · Amount + Region

$8,500 in "North"

Output · Priority?

Priority

=IF(AND(D24="North", C24>5000), "Priority", "")

Download the workbook to try the Decision Tree sheet — edit an amount and watch the nested IF resolve.

The nested IF decision tree

Nested IF is the concept most beginners struggle with. Here's exactly how Excel reads the tier formula from Example 3 — one check at a time, left to right, top to bottom:

How Excel reads the nested IF

=IF(Amount>=10000, "Platinum", IF(Amount>=7000, "Gold", IF(Amount>=4000, "Silver", "Bronze")))

Check #1: Amount >= 10,000? → YES Platinum

Check #2: Amount >= 7,000? → YES Gold

Check #3: Amount >= 4,000? → YES Silver

Default

All checks failed → FALLBACK Bronze

The mental model: think of nested IF as a staircase. Each step checks something. If the check passes, take that step's exit. If not, drop to the next step. The final "else" catches whatever fell all the way through. A $12,000 deal exits at Check #1 (Platinum) and never sees Checks #2 or #3. A $500 deal falls through all three and lands on Bronze.

The IF cheat sheet — 10 patterns for daily use

Copy any of these, adapt the ranges to your data, and you're 80% of the way there. Same 10 patterns are in the workbook's Cheat Sheet sheet:

10 patterns you'll use every day

From simplest (threshold check) to combinatorial (IF with a SUMIFS inside). Every one is a copy-paste starting point.

1Above threshold

=IF(A2>1000,"Big","Small")

The classic. Any comparison operator: > < >= <= = <>

2Text match

=IF(A2="North","Domestic","Intl")

Text comparisons use double quotes. Case-INSENSITIVE by default.

3Return a number

=IF(A2>1000, A2*0.1, 0)

10% commission if above threshold, else zero.

4Return a blank

=IF(A2="", "", A2*2)

Empty string gives a clean blank. Great for conditional columns.

5AND — all must match

=IF(AND(A2>1000,B2="North"),"P","")

Both conditions required. Blank output when not matched.

6OR — any can match

=IF(OR(A2>10000,B2="VIP"),"Flag","")

Either condition triggers. Useful for exception detection.

7NOT — invert a check

=IF(NOT(A2="Done"),"Follow up","OK")

Reverses TRUE/FALSE. Same as IF(A2<>"Done",...)

8Nested for grades

=IF(A2>=90,"A",IF(A2>=80,"B","C"))

3+ outcomes. For 5+ tiers, use IFS or VLOOKUP.

9IFERROR wrap

=IFERROR(A2/B2, 0)

Catches DIV/0, VALUE, REF, NAME, N/A, NULL, NUM.

10IF around a SUMIFS

=IF(SUMIFS(A,B,"N")>50000,"On","Off")

Compare an aggregated total to a target.

IF vs IFS — a side-by-side

Same logic, two ways. When should you reach for IFS instead of nesting IF?

Aspect	Nested IF	IFS (Excel 2019+)
Excel version	Every version (since 1.0)	2019, 2021, 365 · older versions error out
Syntax (3 tiers)	IF(A>=10000,"P",IF(A>=7000,"G","B"))	IFS(A>=10000,"P",A>=7000,"G",TRUE,"B")
Closing parens	3 nested — easy to miscount	1 — always
Default / "else"	Built-in via value_if_false	Need `TRUE` as last condition
Readability at 5+ tiers	Poor — deeply indented	Good — reads like a table
Best for 5+ tiers	Use VLOOKUP against a tier table instead	Use VLOOKUP against a tier table instead

Practical rule: use nested IF for 2-3 tiers when you need cross-version compatibility. Use IFS for 3-5 tiers when you're on Excel 2019+ and readability matters. Use VLOOKUP against a lookup table for 5+ tiers — it's the most maintainable approach for anything with many branches.

Common errors and how to fix them

IF itself rarely errors — but the expressions inside it can. Six common scenarios:

Result	Why it happens	Broken → Fix
NAME error	Text output missing quotes — Excel treats bare words as named ranges.	`=IF(A2>100, Big, Small)` `=IF(A2>100, "Big", "Small")`
FALSE (literal)	Omitted value_if_false — Excel returns the boolean FALSE.	`=IF(A2>100, "Big")` `=IF(A2>100, "Big", "Small")`
Always TRUE	Trailing space in text comparison causes mismatch.	`=IF(A2="North",1,0) but A2 has trailing space` `=IF(TRIM(A2)="North",1,0)`
Wrong tier	Nested IF thresholds in wrong order — first match wins.	`=IF(A>=4000,"Silver",IF(A>=10000,"Platinum",...))` `=IF(A>=10000,"Platinum",IF(A>=4000,"Silver",...))`
VALUE error	Comparing text to number without explicit conversion.	`=IF(A2>"100", ...) // A2 is text` `=IF(VALUE(A2)>100, ...)`
Broken paren count	Deep nested IF has mismatched parentheses.	`=IF(A>10,IF(B>5,"P","Q","R")` `=IF(A>10,IF(B>5,"P","Q"),"R")`

📗 Every example above, in one workbook

Basic IF · AND/OR · Nested tiers · IF vs IFS · IFERROR · Cheat Sheet · Decision Tree.

Download if-examples-2026.xlsx

IF is the primitive underneath everything

🧱 The whole conditional universe builds on IF

Every SUMIFS is "IF this row matches, add its value." Every COUNTIFS is "IF this row matches, count it." Every conditional formatting rule is "IF this cell is red." IFS is a syntactic wrapper around nested IFs. IFERROR is "IF this formula errored." SWITCH is a shorthand for a specific IF pattern. Master IF and every conditional function feels familiar.

Companion functions worth knowing

The IFS family — conditional aggregators built on IF's logic

Excel version compatibility

IF works in every version of Excel ever shipped, and in every spreadsheet clone:

Platform	Supports IF?	Notes
Excel 365 (Windows & Mac)	✓ Yes	Full support
Excel 2021 / 2019 / 2016 / 2013	✓ Yes	Full support
Excel 2010 / 2007 / 2003	✓ Yes	Full support
Excel 2000 / 97 / 95 / 5 / 4 / 3 / 2 / 1	✓ Yes	Been in Excel since day one
Excel for the web	✓ Yes	Full support
Excel on iPad & iPhone	✓ Yes	Full support
Google Sheets	✓ Yes	Same syntax, same behavior
LibreOffice Calc	✓ Yes	Full support
Apple Numbers	✓ Yes	Full support

When to use IF vs. alternatives

Use IF when…

You have exactly 2 possible outcomes. True path, false path. Done.
You have 3 outcomes and need cross-version support. Nested IF works everywhere.
You're wrapping any formula in IFERROR. That's IFERROR = specialized IF.

Use IFS instead when…

You have 3-5 outcomes AND you're on Excel 2019+.
Readability matters (dashboards, shared workbooks).

Use VLOOKUP or INDEX/MATCH instead when…

You have 5+ outcomes based on a numeric range. A lookup table is cleaner.
The mapping might change — a lookup table is editable without touching formulas.

Use SWITCH instead when…

You're matching ONE cell against a list of fixed values (not ranges). SWITCH is designed for that.

Use the IFS siblings (SUMIFS etc.) when…

You're aggregating values from rows that match criteria. Don't wrap IF around SUMPRODUCT — use SUMIFS directly.

How IF actually works

The algorithm

IF evaluates the logical test to a boolean (TRUE or FALSE). If TRUE, it returns whatever's in the second argument. If FALSE, it returns whatever's in the third argument (or FALSE if the third is omitted). Only ONE of the two branches is evaluated — a critical optimization.

Short-circuit evaluation

Excel does NOT evaluate the un-taken branch. This means =IF(B2=0, "Zero", A2/B2) is safe from divide-by-zero errors — when B2 is 0, the division is never attempted. This short-circuit behavior lets you use IF as a guard clause without extra IFERROR wrapping.

Type coercion

Booleans: TRUE = 1, FALSE = 0. So =IF(A2, ...) is TRUE for any non-zero value in A2.
Empty cells: read as 0 for numeric contexts, "" for text. =IF(A2="", ...) is TRUE for blank cells.
Numbers as text: "5" and 5 are NOT equal. Use VALUE() to convert text-numbers.

Nesting limits

Excel 2007+ allows up to 64 nested IFs. If you're approaching that limit, stop — you're building something unmaintainable. Refactor into a lookup table or use IFS.

Performance notes

IF is essentially free at any reasonable scale. Two considerations for extreme cases:

Volatile functions in un-taken branches. Even though IF short-circuits, if you have =IF(A2, TODAY(), 0) across 100,000 rows, TODAY() is still volatile — the whole column recomputes on every recalc. Not IF's fault, but common with IF.
Deep nesting. 20-level nested IF calculates 20 times slower than a single-level VLOOKUP against a 20-row tier table. Use lookup tables for many tiers.

When to switch to a lookup table

If your nested IF exceeds 5 levels, refactor to a two-column tier table and use VLOOKUP or INDEX/MATCH. Editing thresholds becomes trivial, the formula becomes readable, and performance improves.

How to write an IF from scratch

Write the question
What are you checking? "Is the deal above 5000?" "Is the region North?" State it clearly before you touch the formula.
Write the logical test
Translate the question to a comparison. "Above 5000" becomes A2>5000. This goes in the first argument slot.
Add value_if_true — the "YES" answer
What should IF return when the test passes? Text (in quotes), a number, or another formula.
Add value_if_false — the "NO" answer
Even if you'd default to blank, always specify "" for clarity. Never rely on the implicit FALSE return.
For 3+ outcomes, replace value_if_false with another IF
The "no" answer becomes the next check. Order from most-specific (highest threshold) to most-general (default).

Functions used with IF

IFERROR IFS AND OR NOT SWITCH ISBLANK ISNUMBER ISTEXT EXACT TRIM VALUE SUMIFS COUNTIFS VLOOKUP

🎁 Grab the free IF workbook

9 sheets covering everything on this page — basic IF, AND/OR, nested tiers, IF vs IFS, IFERROR patterns, decision tree, cheat sheet.

Download if-examples-2026.xlsx

Frequently asked questions

What's the difference between IF and IFS?

IF handles one condition (with an implicit "else"). IFS handles multiple conditions in one formula — no nesting required. IFS shipped with Excel 2019; older versions return a NAME error. IFS reads cleaner at 3-5 conditions but requires TRUE as the last condition to act as the default.

How deep can I nest IF statements?

Excel 2007+ allows up to 64 levels of nesting. Excel 2003 and earlier maxed at 7 levels. Anything above 5 levels is a code smell — refactor to IFS, VLOOKUP against a lookup table, or SWITCH.

Why does my IF return FALSE instead of my value?

You omitted the value_if_false argument. When the logical test is FALSE and no third argument exists, IF returns the literal boolean FALSE. Always specify all three arguments — use "" for a blank result if that's what you want.

Can IF return a formula instead of a value?

Yes — either branch can be a formula, a cell reference, or a function call. =IF(A2>0, SUM(B:B), AVERAGE(C:C)) works. Only the taken branch is evaluated (short-circuit).

Is IF case-sensitive for text comparisons?

No — "North", "NORTH", and "north" all match in an IF comparison. For case-sensitive comparisons, wrap with EXACT: =IF(EXACT(A2, "North"), ...).

Why does IF give a NAME error?

Almost always because a text output isn't in quotes. =IF(A2>100, Yes, No) fails because Excel treats Yes and No as named ranges (which don't exist). Correct: =IF(A2>100, "Yes", "No").

Can IF check for text in a specific position?

Yes — combine IF with SEARCH or FIND: =IF(ISNUMBER(SEARCH("Pro", A2)), "Premium", "Standard"). SEARCH returns a position number for matches, an error otherwise, so ISNUMBER cleanly converts to TRUE/FALSE.

How do I write "IF this cell is blank"?

Three options: =IF(A2="", ...), =IF(ISBLANK(A2), ...), or =IF(LEN(A2)=0, ...). ISBLANK is strictest — it's FALSE for cells with formulas that return "". Use ="" for the "looks blank to the user" check.

What's the difference between IF and IFERROR?

IF checks a condition YOU write. IFERROR checks whether a formula produced an error. Use IFERROR to gracefully handle expected failures (missing lookup, zero denominator) without a manual condition check.

Does IF work in Google Sheets?

Yes, identically. Google Sheets, LibreOffice Calc, and Apple Numbers all implement IF with the same syntax and semantics as Excel.

Can I use IF with dates?

Yes — dates are stored as numbers internally, so any comparison works. =IF(A2>=DATE(2026,1,1), "This year", "Older"). Use DATE(y,m,d) to write literal dates, or reference a cell containing a date.

Which templates use IF?

All 10 of our templates use IF somewhere — it's the foundation of conditional logic. Prominently: Budget Tracker (over/under budget flags), Expense Report (policy compliance checks), Sales Dashboard (target-hit indicators), KPI Dashboard (traffic-light status), Invoice (payment status), Loan Calculator (early-payoff logic), Timesheet (overtime detection), Attendance (present/absent tags), Inventory Tracker (reorder alerts), and Project Timeline (deadline status).

Templates that use IF

Every one of our 10 templates uses IF — it's the foundation of conditional logic:

Skip the syntax. Ask in plain English.

The Sheets & Cells AI Add-in writes IF, nested IF, IFS, IFERROR, and every combination in between — right inside Excel. Type "flag deals above 5000 in North as Priority" and get the working formula, ready to paste.

Try the AI Add-in →