IF, AND, OR, IFS and IFERROR: make your formulas decide, check conditions and handle errors cleanly.
8 articles
AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE. NOT flips TRUE to FALSE and FALSE to TRUE. On their own they give TRUE or FALSE, so you usually put them inside IF's test, like =IF(AND(C2>=50000,D2>=4.5),"Bonus","No bonus").
In Excel, "not equal to" is written <>, so =A2<>"Delivered" returns TRUE when A2 is anything other than Delivered. Excel has six comparison operators: = equal, <> not equal, > greater than, < less than, >= greater than or equal, and <= less than or equal. Each returns TRUE or FALSE.
IFERROR returns a formula's normal result, or a value you choose if the formula gives an error: =IFERROR(value, value_if_error). For example, =IFERROR(B2/C2,0) shows 0 instead of #DIV/0!. It catches every error type, including mistakes in your own formula, so check the formula works first, and use IFNA for lookups.
ISBLANK returns TRUE when a cell is completely empty, ISNUMBER returns TRUE when it holds a real number, and ISTEXT returns TRUE when it holds text. Each takes one cell, like =ISBLANK(C2). They are most useful inside IF, for example =IF(ISNUMBER(C2),"OK","Check"), to catch bad data before you calculate with it.
A nested IF puts a second IF in the value_if_false slot of the first, so one formula can return three or more results. For grades: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))). Excel checks the tests in order and stops at the first TRUE one, so test the highest band first.
The Excel IF function checks a condition and returns one value when it's TRUE and another when it's FALSE. The syntax is =IF(logical_test, value_if_true, value_if_false). For example, =IF(B2>=70,"Pass","Fail") shows Pass for a score of 70 or more and Fail for anything lower. Text results go in double quotes.
The Excel IFS function checks a list of conditions in order and returns the result paired with the first TRUE one: =IFS(test1, value1, test2, value2, ...). It replaces nested IFs without the brackets. IFS has no "otherwise" result, so end with TRUE and a fallback value. It needs Excel 2019 or later.
The Excel SWITCH function compares one value with a list of options and returns the result paired with the first exact match: =SWITCH(D2,"P","Pending","S","Shipped","Other"). The last single value is the default, shown when nothing matches. Without a default, SWITCH returns #N/A. SWITCH needs Excel 2019 or later.