IF Formula Guide: Nested IF, IFS & Conditional Logic

What Is the IF Function?

The IF function is one of the most fundamental and widely used functions in spreadsheets. It performs a logical test and returns one value when the condition is true and a different value when it is false. Think of it as a decision-making tool built directly into your cells.

Conditional logic is at the heart of data analysis. Whether you are grading student scores, calculating commissions, flagging overdue invoices, or categorizing sales performance, the IF function lets your spreadsheet make decisions automatically instead of requiring manual review of every row.

Every major spreadsheet platform supports IF: Microsoft Excel, Google Sheets, and Apple Numbers all use identical core syntax. Once you master IF in one platform, the knowledge transfers directly to the others.

IF Syntax

The IF function takes exactly three arguments:

=IF(logical_test, value_if_true, value_if_false)

• logical_test — The condition to evaluate. This must resolve to TRUE or FALSE. Examples: A1>100, B2="Yes", C3"".
• value_if_true — The value returned when the condition is TRUE. Can be text, a number, a cell reference, or another formula.
• value_if_false — The value returned when the condition is FALSE. Same flexibility as value_if_true.

The value_if_false argument is technically optional. If you omit it and the condition is FALSE, the function returns FALSE by default. However, explicitly setting this argument is considered best practice for clarity and to avoid unexpected results.

Basic IF Examples

1. Grade Pass/Fail

The most classic IF use case. Suppose column A contains student scores and you want to mark each as "Pass" (60 or above) or "Fail":

=IF(A2>=60, "Pass", "Fail")

If cell A2 contains 75, the formula returns "Pass". If A2 contains 45, it returns "Fail". The comparison operator >= means "greater than or equal to."

2. Discount Based on Quantity

Apply a 10% discount when a customer orders 100 or more units. Column B has the quantity, column C has the unit price:

=IF(B2>=100, C2*0.9, C2)

When the quantity in B2 is 100 or more, the unit price is multiplied by 0.9 (a 10% reduction). Otherwise, the original price is returned unchanged.

3. Status Check with Empty Cell Handling

Check whether a cell is empty and display a message accordingly. This is useful for tracking task completion where column D contains a completion date:

=IF(D2="", "Pending", "Complete")

An empty string "" is the standard way to test for blank cells in an IF formula. If D2 is empty, the result is "Pending"; otherwise "Complete".

Nested IF Formulas

When you need to test more than two outcomes, you can nest IF functions inside each other. A nested IF places another IF as the value_if_false (or value_if_true) argument, creating a chain of conditions evaluated in order.

For example, assign letter grades based on a score in A2:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

This formula checks conditions from highest to lowest. If A2 is 85, the first test (>=90) fails, the second test (>=80) succeeds, so "B" is returned. The remaining nested IFs are never evaluated.

Order matters in nested IFs. Always arrange conditions from most restrictive to least restrictive (or highest to lowest). If you tested >=60 first, every score above 60 would return "D" and never reach the higher-grade checks.

Excel supports up to 64 levels of nesting, while Google Sheets has no hard limit. However, deeply nested IFs become difficult to read and maintain. If you find yourself nesting more than 3-4 levels, consider using IFS, SWITCH, or a lookup-based approach instead.

IFS Function -- The Modern Alternative

The IFS function was introduced to eliminate the complexity of deeply nested IF statements. Instead of embedding one IF inside another, IFS lets you list all your conditions and results in a flat structure:

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")

Each pair of arguments is a condition followed by its result. Conditions are evaluated left to right, and the first TRUE condition wins. The TRUE at the end acts as a catch-all default (equivalent to "else").

When to use IFS vs nested IF: Use IFS when you have three or more conditions with distinct outcomes. The flat syntax is easier to read, audit, and modify. Use nested IF when you only have two or three conditions, or when you need to maintain backward compatibility with older Excel versions.

Platform support: IFS is available in Excel 2019 and later (and Microsoft 365), Google Sheets, and Apple Numbers. It is not available in Excel 2016 or earlier. If your file may be opened in older Excel versions, stick with nested IF.

IFERROR and IFNA

Formulas sometimes produce errors like #N/A, #VALUE!, or #DIV/0!. The IFERROR and IFNA functions let you handle these gracefully instead of displaying raw error codes to users.

IFERROR

IFERROR catches any error type. It tries to evaluate the first argument; if an error occurs, it returns the second argument instead:

=IFERROR(VLOOKUP(E1, A:B, 2, FALSE), "Not found")

If the VLOOKUP finds a match, its result is returned. If it throws a #N/A error (no match), the formula returns "Not found" instead.

IFNA

IFNA is more specific -- it only catches #N/A errors and lets other errors pass through. This is safer because it does not accidentally hide genuine problems like #REF! or #VALUE!:

=IFNA(VLOOKUP(E1, A:B, 2, FALSE), "Not found")

Best practice: use IFNA when you specifically expect #N/A from lookup functions (VLOOKUP, INDEX/MATCH, XLOOKUP). Use IFERROR when you want to suppress all possible error types, such as when dividing numbers that might be zero.

Combining IF with Other Functions

The real power of IF emerges when you combine it with other spreadsheet functions. Here are the most common and useful combinations.

IF + AND (Multiple Conditions, All Must Be True)

Check if a sale qualifies for a bonus: the amount must be over $1,000 AND the region must be "West":

=IF(AND(B2>1000, C2="West"), "Bonus", "Standard")

The AND function returns TRUE only when every condition inside it is TRUE. Here, both the amount check and the region check must pass.

IF + OR (Multiple Conditions, Any Can Be True)

Flag a row if the status is either "Overdue" or "Cancelled":

=IF(OR(D2="Overdue", D2="Cancelled"), "Action Required", "OK")

The OR function returns TRUE when at least one condition is TRUE. This is cleaner than writing two separate nested IF statements.

IF + VLOOKUP

Look up a product price and apply a tax if the product category is "Taxable":

=IF(VLOOKUP(A2, Products!A:C, 3, FALSE)="Taxable", B2*1.2, B2)

This formula uses VLOOKUP to retrieve the category from a separate sheet and then applies conditional logic based on the result. Wrapping this in IFERROR would add an extra layer of robustness.

Platform Differences: Excel vs Google Sheets vs Numbers

The core IF syntax is identical across all three platforms. However, there are differences in related functions and behavior:

Excel: Has the broadest function library. XLOOKUP, SWITCH, and LET are available in Excel 2019+ and Microsoft 365. Older versions (2016 and earlier) require nested IF for multi-condition logic.

Google Sheets: Supports IFS, SWITCH, and LAMBDA. Also has unique functions like QUERY and ARRAYFORMULA that can sometimes replace complex IF logic entirely.

Apple Numbers: Supports IF, IFS, and IFERROR but lacks SWITCH, IFNA, and LAMBDA. For complex conditional logic in Numbers, nested IF or IFS are your primary options.

Related Guides

• VLOOKUP Guide — Look up values in a table by matching a key in the first column.
• SUMIF & SUMIFS Guide — Sum values that meet one or more conditions.
• INDEX MATCH Guide — The powerful, flexible alternative to VLOOKUP.
• FAQ — Frequently asked questions about FormulaAI.