IF, IFS and logical tests

Conditional logic without nesting yourself into a corner.

What you'll be able to do

Write IF, nested IF and IFS formulas
Combine conditions with AND, OR and NOT
Handle errors with IFERROR and IFNA

IF

=IF(test, value_if_true, value_if_false)

=IF(B2>1000, "Large", "Small")

The test can be any expression returning TRUE or FALSE. Text comparisons are case-insensitive, which surprises people — "abc"="ABC" is TRUE in Excel.

Nesting, and why IFS is better

Nested IFs get unreadable fast:

=IF(B2>1000,"A",IF(B2>500,"B",IF(B2>100,"C","D")))

IFS flattens it:

=IFS(B2>1000,"A", B2>500,"B", B2>100,"C", TRUE,"D")

Conditions are tested in order and the first TRUE wins — so order matters, and the final TRUE acts as the catch-all. SWITCH is cleaner still when matching one value against a list of exact options.

Combining conditions

AND(cond1, cond2) is TRUE only if all are TRUE. OR(...) is TRUE if any is. NOT(...) inverts.

=IF(AND(B2>1000, C2="East"), "Priority", "Standard")

A useful trick: TRUE behaves as 1 and FALSE as 0 in arithmetic, so =SUMPRODUCT((B2:B100>1000)*(C2:C100="East")) counts rows meeting both conditions.

Error handling

=IFERROR(formula, fallback) catches every error type. =IFNA(formula, fallback) catches only #N/A, which is usually the better choice with lookups — you still want to see a genuine #DIV/0! rather than hide it.

Wrapping everything in IFERROR is a common bad habit. It hides real bugs. Catch the specific error you expect.

Key points

IFS reads better than nested IF; conditions test in order
TRUE/FALSE act as 1/0 in arithmetic — the basis of SUMPRODUCT counting
Prefer IFNA over IFERROR with lookups so real errors stay visible

Check yourself