Logical Functions - Sheets and Cells

Logical functions are how you make Excel formulas think. "If revenue is above target, mark green — otherwise red." "If date is a weekend AND priority is high, flag urgent." "If any of these three conditions is true, notify." Without logical functions, formulas can only calculate. With them, formulas can decide.

The essential four

Most used

IF

=IF(condition, value_if_true, value_if_false)

The foundational logical function. Test a condition, return one value if true, another if false. Every Excel user learns IF first — and never stops using it.

IF Complete Guide →

Cleaner than nested IFs

IFS

=IFS(cond1, val1, cond2, val2, ...)

Multiple conditions without messy nesting. Instead of five nested IFs to grade A/B/C/D/F, one clean IFS. Excel 2019+ only.

IFS Complete Guide →

Every formula should use this

IFERROR

=IFERROR(formula, value_if_error)

Catch errors and return a fallback value. Wrap risky formulas (lookups, divisions) to prevent #N/A or #DIV/0! from appearing in reports.

IFERROR Complete Guide →

AND / OR / NOT

=IF(AND(A1>0, B1<100), "in range", "out")

Combine multiple conditions. AND requires all true. OR requires any true. NOT inverts. Used inside IF to build complex conditions.

AND / OR / NOT Guide →

Which function for which decision?

IF One condition, two outcomes. "Is this over target? Yes or no?"

IFS Multiple conditions, multiple outcomes. Grade brackets, tier assignments, category classifications.

SWITCH One value, many possible matches. Cleaner than IFS when comparing one variable to a list of options.

AND Combined ALL conditions must be true. "Weekend AND urgent AND unassigned."

OR ANY of the conditions is enough. "Overdue OR high-value OR VIP customer."

NOT Invert a condition. "NOT weekend" = weekday.

IFERROR Any formula that might fail (lookups, divisions, external data). Provides a graceful fallback.

IFNA Only catches #N/A specifically. Useful when you want other errors to still surface.

Common patterns

Nested IF for tiers: =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))) — clear but ugly beyond 3 tiers. Switch to IFS.

IFS for the same tiers: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") — the final TRUE catches everything else.

IFERROR wrapping VLOOKUP: =IFERROR(VLOOKUP(A1, table, 2, FALSE), "Not found") — no more #N/A in reports.

AND inside IF: =IF(AND(price>100, stock>0), "Ready", "No") — both conditions required.

OR for flexible matching: =IF(OR(A1="urgent", A1="critical"), "🚨", "") — either label triggers the flag.

Frequently asked questions

How many IFs can I nest inside each other?

Excel allows up to 64 nested IFs, but nobody should nest more than 3. Past 3, use IFS (multiple conditions), SWITCH (matching to a list), or a lookup table with VLOOKUP/XLOOKUP instead.

What's the difference between IF blank and IF empty string?

=IF(A1="", ...) checks if the cell is blank OR contains an empty string. =IF(ISBLANK(A1), ...) only matches truly empty cells — formulas returning "" don't count. Choose based on your data source.

IFERROR or IFNA — which should I use?

IFERROR catches ALL errors and hides them. IFNA catches only #N/A. Use IFNA when you want to see other errors (like #DIV/0! or #REF!) so you can catch real bugs — while gracefully handling missing lookups.

Can AND and OR handle more than 2 conditions?

Yes — up to 255 arguments. =AND(A1>0, B1<100, C1="active", D1>=today()) tests all four at once. But if you have 10+ conditions, restructure — a lookup table is usually cleaner.

Why does my IF return 0 instead of blank?

Excel treats empty and 0 as similar. Use =IF(A1="", "", A1+B1) to explicitly return empty string when the source is blank, instead of the default 0.

Complex logic, simple prompts.

Describe your decision — "if invoice is over 30 days late AND amount is over $1000, flag urgent — otherwise normal" — and get the exact nested formula.

Get the Add-in →