IF, IFS, SWITCH, AND & OR: Excel Conditional Formulas Made Simple
The editor reproduced and verified these examples in Excel 365 and used AI tools only as an aid in organizing the draft.
Syntax and key concepts
| Function | Format | Key point |
|---|---|---|
| IF | =IF(condition, value_if_true, value_if_false) | Two-way branching. Use AND/OR for compound conditions. |
| IFS | =IFS(condition1,result1, condition2,result2, …) | Evaluates from top to bottom and returns the first TRUE result. |
| SWITCH | =SWITCH(expression, value1,result1, value2,result2, …, [default]) | Best for mapping fixed values to labels. |
| AND/OR | =AND(condition1,condition2,…) / =OR(…) | Creates compound conditions. |
Boolean rules: TRUE=1 and FALSE=0. In formulas, multiplication (*) works as AND and addition (+) works as OR, which pairs well with FILTER and SUMPRODUCT.
IF with AND/OR basics
Attendance and score conditions together
=IF(AND(Score>=90, Absence=0), "A", "B~F")VIP or recent purchaser
=IF(OR(VIP="Y", TODAY()-LastPurchase<=30), "Priority Customer", "Regular")Protect against blanks and zeros
=IF(OR(A2="", A2=0), "", A2*Rate)IFS — minimize nesting
Grade labels
=IFS(Score>=90,"A", Score>=80,"B", Score>=70,"C", TRUE,"D")Status code to label
=IFS(Status="NEW","New", Status="PENDING","In Progress", Status="DONE","Complete", TRUE,"Other")SWITCH — map values
Map five status codes, including a default
=SWITCH(Status, "N","New","P","Pending","R","Rejected","D","Complete","U","On Hold","Other")Weekday number to weekday name
=SWITCH(WEEKDAY(Date,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun")12 practical patterns
① Price tiers
=IFS(Price<10000,"Entry", Price<50000,"Standard", Price<100000,"Pro", TRUE,"Elite")② Shipping policy (free or paid)
=IF(Amount>=Threshold, 0, Fee)③ Return-risk label (includes text)
=IF(OR(ISNUMBER(SEARCH("defect", LOWER(Desc))), ReturnDays>30), "High","Normal")④ Protect against user error: division by zero
=IF(Den=0, "", Num/Den)⑤ Multiple conditions with spill (dynamic arrays)
=IF((Region="Seoul")*(Month=EOMONTH(TODAY(),0)), "Y","N")⑥ KPI traffic light (emoji/symbol)
=SWITCH(TRUE, Rate>=0.95,"🟢", Rate>=0.9,"🟡", TRUE,"🔴")⑦ Replace missing values (average/median)
=IF(Value="", AVERAGEIF(Value,"<>",Value), Value)⑧ Customer tier and benefit label
=SWITCH(Tier,"GOLD","Free Shipping","SILVER","10% Coupon","BRONZE","2% Rewards","Regular")⑨ Text category plus a condition
=IF(AND(Category="Keyboard", Sales>=1000000), "Hit", "Regular")⑩ SLA overdue based on business days
=IF(WORKDAY(Start,N,,Holiday)<=Due,"On-time","Delay")⑪ Points by score range
=VSTACK({"Range","Points"}, {">=90","100" ; "80~89","70" ; "70~79","40" ; "<70","10"})⑫ Switch from IFS to XLOOKUP (replace with a rules table)
// Rules table Cutline:Grade (lower-bound table)
=XLOOKUP(Score, Cutline, Grade, , -1)Improve readability and performance with LET
=LET(s, Score, a, Absence,
IF(OR(s="", a=""), "", IFS(s>=90,"A", s>=80,"B", s>=70,"C", TRUE,"D")))TIP Caching repeated references, such as Score, in LET variables can improve speed in large tables and make debugging easier.
Common mistakes and checks
- Mistaking text “TRUE”/”FALSE” for Boolean values — quotation marks make them text strings.
- Comparison operator precedence — do not use
A<B<C. UseAND(A<B, B<C)instead. - Confusing blanks, 0, and NA — define a policy and add defensive logic in the first IF branch.
- Long nesting — replace it with IFS/SWITCH or XLOOKUP with a rules table to improve maintainability.
Summary
| Goal | Typical formula |
|---|---|
| Compound conditional branching | IF(AND/OR(...), value1, value2) |
| Multiple ranges or labels | IFS(condition1,label1, ... , TRUE,default) |
| Value mapping | SWITCH(expression, value,label, …, default) |
| Rules-table based | XLOOKUP(score, Cutline, Grade, , -1) |
| Performance and readability | LET(variables, …) |