Nested IF, AND, OR and IFERROR: Decision Formulas Without the Headache

📎 This article includes 1 downloadable practice file ↓

Grades, commission slabs, eligibility checks, status labels: decision formulas are everywhere, and they’re where silent mistakes hide. The rules below keep them correct and readable.

Nested IF: strictest first

=IF(B2>=90, "A+", IF(B2>=75, "A", IF(B2>=60, "B", IF(B2>=40, "C", "F"))))

Excel stops at the first TRUE. Put the highest threshold first; if you test B2>=40 first, everyone with 40 or more gets “C”.

IFS: the same, flatter (Excel 2019+)

=IFS(B2>=90, "A+", B2>=75, "A", B2>=60, "B", B2>=40, "C", TRUE, "F")

The final TRUE, "F" is the catch-all; without it, unmatched values return #N/A.

AND and OR

=IF(AND(B2>=40, C2>=75%), "Eligible", "Not eligible")      ' marks AND attendance
=IF(OR(D2="Gold", E2>500000), "Priority", "Standard")
=IF(AND(B2>=40, OR(C2>=75%, F2="Medical")), "Eligible", "Not eligible")

SWITCH for exact matches

=SWITCH(A2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")

When to stop nesting: a lookup table

More than four levels, or thresholds that change every year (tax slabs, commission rates)? Put them in a table and use an approximate lookup:

=XLOOKUP(B2, Bands[From], Bands[Grade], , -1)
=VLOOKUP(B2, $H$2:$I$6, 2, TRUE)

Changing a threshold then means editing a cell, not rewriting formulas. See approximate match for slabs.

IFERROR and IFNA, carefully

=IFNA(XLOOKUP(A2, Codes, Names), "Code not found")     ' only #N/A
=IFERROR(B2/C2, 0)                                      ' any error

Where people go wrong