When Your Nested IFs Go Three Levels Deep, Switch to IFS — Sorting Out IF / AND / OR / IFERROR

“Pass if the score is 80 or above.” “120% of target is an S rank, 100% is an A…” “If the target hasn’t been entered yet, show a blank instead of an error” — business spreadsheets are full of conditional logic.

The workhorse is IF. It’s the first function everyone learns, but once you have three or four conditions you end up with IF(IF(IF(...))) nesting, and the parentheses become impossible for a human to track. This article sorts out the whole conditional toolbox: IFS for flattening the nesting, AND / OR for combining conditions, and IFERROR for turning errors into sensible defaults. Then we’ll use ReoGrid (IFS / IFERROR supported since V4.5) to run the very same formulas inside a WinForms / WPF app.

This is part of our series on the classic Excel functions, following the lookup installment: VLOOKUP / HLOOKUP / XLOOKUP and MATCH / XMATCH. This time: branching on conditions.

The short version — four tools, four jobs

Two points to remember:

1. Two outcomes → IF; three or more → IFS. Nested IF is fine up to two levels — beyond three, rewrite it as IFS
2. AND / OR aren’t standalone functions in practice — they’re building blocks you put into the condition slot of IF or IFS

IF — a two-way branch on one condition

IF(condition, value_if_true, [value_if_false]). The foundation of everything.

=IF(B2>=80, "Pass", "Fail")        Pass at 80 points or above
=IF(C2="", "Missing", "Submitted") Blank check

One small gotcha: the third argument is optional, but if you omit it, the cell displays the word FALSE. If you want “no match” to show as a blank, write "" explicitly.

=IF(B2>=80, "Pass")        → failing rows display FALSE
=IF(B2>=80, "Pass", "")    → failing rows stay blank

Nested-IF hell, and the IFS rewrite

Say you want to grade scores into four tiers, S / A / B / C. With IF alone:

=IF(B2>=90, "S", IF(B2>=70, "A", IF(B2>=50, "B", "C")))

Three levels is still readable, but at five or six tiers you can no longer track the parentheses — one misplaced closing paren and the whole formula errors out. Whoever inherits the sheet gets to decipher it. This is nested-IF hell.

With IFS, the same logic becomes a flat list of condition, value pairs:

=IFS(B2>=90, "S", B2>=70, "A", B2>=50, "B", TRUE, "C")

Three rules to remember:

1. Pairs are evaluated top to bottom, and the first true condition wins. So order the conditions from strictest (narrowest) to loosest — if B2>=50 comes first, it swallows the 90s and 70s too
2. If no condition matches, the result is an error. The idiom for an “everything else” default is to end with a TRUE, value pair (TRUE always matches, so anything that falls through gets caught there)
3. The argument count must be even (condition/value pairs) — an odd count is an error

AND / OR / NOT — combining conditions

When one branch depends on several conditions — “written exam and practical both at 80 or above” — combine them with AND / OR inside IF’s condition slot.

=IF(AND(B2>=80, C2>=80), "Pass", "Fail")      pass only if both are 80+
=IF(OR(D2="Absent", B2<30), "Retake", "-")    retake if absent or below 30
=IF(NOT(E2="Excluded"), "Included", "")       include unless marked excluded

• AND(cond1, cond2, …) — true when all are true
• OR(cond1, cond2, …) — true when any one is true
• NOT(cond) — inverts true/false

The same works in IFS condition slots. Compound rules like “award a bonus when the achievement rate is at least 100% and actual sales are at least 300” drop straight into the IF(AND(...), ...) shape.

IFERROR — turning errors into defaults

Alongside branching, the other must-have in business sheets is error handling. VLOOKUP returning #N/A when a key isn’t found, a divide-by-zero because a target cell is still empty — the math may be right, but a screen full of error codes is unusable as a report.

Wrap the formula in IFERROR(formula, value_if_error) and errors are replaced by the value you choose:

=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not registered")
=IFERROR(D2/C2, "")                                              blank on divide-by-zero

IFERROR catches every error type, including #N/A. If you only need “is this an error?” as a condition, the test functions ISERROR / ISNA / ISBLANK combined with IF work too.

Note that XLOOKUP takes a “value if not found” as its fourth argument directly, so it doesn’t need an IFERROR wrapper. Think of IFERROR as the go-to technique wherever you’re using VLOOKUP or MATCH.

Running it inside a C# app — ReoGrid V4.5

Now for the main event. These are all Excel functions, but with ReoGrid they evaluate right inside your app — no Office installation required. IF / AND / OR / NOT have been supported for a long time; IFS / IFERROR arrived in V4.5.

Here’s a sales report that computes achievement rates and assigns S / A / B / C ranks:

using unvell.ReoGrid;

var sheet = grid.CurrentWorksheet;

// Sales results (B: rep, C: target, D: actual). Taylor's target is unset (0)
sheet.SetRangeData("B2:D6", new object[,]
{
    { "Smith",  500, 620 },
    { "Suzuki", 500, 480 },
    { "Taylor",   0, 150 },
    { "Tanaka", 400, 260 },
    { "Ito",    300, 360 },
});

for (int r = 2; r <= 6; r++)
{
    // Achievement rate. A zero target means divide-by-zero, so IFERROR blanks it
    sheet[$"E{r}"] = $"=IFERROR(D{r}/C{r}, \"\")";

    // Rank: 120%+ = S / 100%+ = A / 70%+ = B / below = C.
    // Rows with a blank rate are filtered out first with IF, then handed to IFS
    sheet[$"F{r}"] = $"=IF(E{r}=\"\", \"-\", IFS(E{r}>=1.2, \"S\", E{r}>=1, \"A\", E{r}>=0.7, \"B\", TRUE, \"C\"))";

    // Compound condition: rate >= 100% AND actual >= 300 earns an award
    sheet[$"G{r}"] = $"=IF(AND(E{r}>=1, D{r}>=300), \"Award\", \"\")";
}

// Read the computed results back
var smithRank = sheet.GetCellData<string>("F2");   // "S"  (124% of target)
var itoAward  = sheet.GetCellData<string>("G6");   // "Award" (120%, actual 360)

Nested combinations — an IFS in the false branch of an IF, a VLOOKUP wrapped in IFERROR — evaluate as-is. Put the thresholds (1.2, 0.7) in their own cells and reference them, and you have a rank table whose grading criteria can be edited on screen — in a few lines of code. And when a user types =IFS(...) directly into a cell, it recalculates the same way.

The blank check at the front of column F is there for a reason. Comparing an empty string against a number is treacherous territory that behaves differently across environments — in Excel, text always compares greater than any number, so a blank row would sail straight into an "S" rank. If a cell can contain anything non-numeric, filter it out with IF before handing it to IFS — the pattern that behaves safely in both ReoGrid and Excel.

ReoGrid implementation notes:

• AND / OR accept individual (scalar) conditions only. The whole-range form you sometimes see in Excel, like =AND(B2:B10>0), is not supported at this time — list the conditions separated by commas instead
• NOT takes a logical value only (no implicit numeric conversion — =NOT(1) won’t work)
• String comparison with = is case-sensitive (Excel’s is not). For data with inconsistent casing or mixed full-width / half-width characters, normalize first with UPPER / ASC before comparing

Summary

• Two outcomes → IF; three or more → IFS. Once nested IF passes three levels, it’s time to rewrite
• IFS evaluates top to bottom, so order conditions from narrowest to loosest, and end with a TRUE, value pair as the default
• AND / OR / NOT are building blocks for the condition slot of IF and IFS — use them to combine compound rules
• IFERROR turns #N/A, divide-by-zero, and every other error into the default of your choice — a natural companion to VLOOKUP
• ReoGrid V4.5 supports IFS / IFERROR. The same formulas run inside your WinForms / WPF app, no Office required

With lookups and conditional branching covered, most of the formula toolbox a business app needs is now on the table.

See what’s new in ReoGrid 4.5 / Try the 30-day trial

Read next

• VLOOKUP vs HLOOKUP vs XLOOKUP — Knowing the Difference and Using Them in a C# Spreadsheet
• MATCH vs XMATCH — The Lookup Functions That Return a Position
• Announcing ReoGrid 4.5 — 47 Excel-Compatible Functions and Point-Mode Formula Editing
• Excel-Style Conditional Formatting in a .NET Application