IF, IFS, AND and OR in Excel: Writing Conditional Formulas
Basics4 minInnovialabs editorial team
In short
IF checks one condition and returns one of two values, AND and OR combine several checks, IFS replaces nested IFs, and when there are many thresholds a lookup table with VLOOKUP is easier to maintain.
The short answer: how IF works
IF tests a condition and returns one value when it is true and another when it is false:
=IF(condition, value_if_true, value_if_false)
Example: a customer with an order of 100,000 or more is wholesale.
=IF(B2>=100000, "Wholesale", "Retail")
In English Excel the arguments are separated by commas. In some regional settings (Russian, for example) the separator is a semicolon and the function names are localized: ЕСЛИ, И, ИЛИ, ЕСЛИМН. Google Sheets accepts the English names; its separator depends on the spreadsheet locale.
Comparison operators
| Operator | Meaning | Example |
|---|---|---|
= | equal to | C2="Paid" |
<> | not equal to | C2<>"" (cell is not empty) |
> < | greater, less | B2>0 |
>= <= | greater or equal, less or equal | D2<=TODAY() |
Text always goes in quotes, numbers never do. Text comparison inside IF is not case-sensitive: "paid" and "Paid" match.
Several conditions with AND and OR
- AND returns TRUE only when every condition is met.
- OR returns TRUE when at least one condition is met.
Ship an order only if it is large and paid:
=IF(AND(B2>=100000, C2="Paid"), "Ship", "Wait")
Give a discount to a regular customer or for an order of more than 10 items:
=IF(OR(D2="Regular", E2>10), "Discount", "No discount")
You can combine them: AND(B2>0, OR(F2="Tashkent", F2="Samarkand")). When the logic gets heavy, move part of it into a helper column. The formula becomes easier to read and debug.
Nested IFs
When there are more than two outcomes, one IF goes inside another. Example: a customer tier based on total purchases:
=IF(B2>=500000, "Platinum", IF(B2>=200000, "Gold", IF(B2>=50000, "Silver", "Basic")))
The key rule is the order of checks. Go from the strictest condition to the loosest. If you test B2>=50000 first, every customer from 50,000 upward gets "Silver" and the other checks never run.
IFS instead of nested IFs
IFS takes "condition, result" pairs and returns the result of the first true condition:
=IFS(B2>=500000, "Platinum", B2>=200000, "Gold", B2>=50000, "Silver", TRUE, "Basic")
- Fewer brackets, easier to read.
- The final pair
TRUE, "Basic"acts as "everything else". Without it IFS returns#N/Awhen no condition matches. - The order of checks matters just as it does with nested IFs.
- IFS exists in Google Sheets and in current Excel versions; older Excel versions don't have it and will show an error.
When to switch to a lookup table
If you have more than three or four thresholds, or they change, don't keep them inside the formula. Create a separate sheet called "Tiers":
| Threshold | Tier |
|---|---|
| 0 | Basic |
| 50000 | Silver |
| 200000 | Gold |
| 500000 | Platinum |
Then find the tier with an approximate match. The thresholds must be sorted in ascending order:
=VLOOKUP(B2, Tiers!$A$2:$B$5, 2, TRUE)
The benefits: thresholds are visible and can be changed without editing formulas, and every report uses the same reference table. For exact matches ("region code → name") use VLOOKUP with FALSE or XLOOKUP.
Common mistakes
- Numbers stored as text.
B2>=100000gives the wrong result if B2 holds the text "150000". Check the alignment and the cell format. - The wrong separator. A comma in a locale that expects semicolons causes a syntax error.
- Quotes around numbers.
B2>"100"compares a number with text and doesn't behave as expected. - Empty cells. An empty cell counts as zero, so
B2<1000is true for it. Add a check likeB2<>"". - Too many nesting levels. If the formula no longer fits in the formula bar, that is your signal to switch to IFS or a lookup table.