The AND function returns TRUE only when every condition is true. The OR function returns TRUE when at least one condition is true. The IFS function checks several conditions in order and returns the result for the first one that's true. Used with IF, these three let you handle many conditions cleanly, instead of writing a long, confusing nested IF. IFS is available in Excel 2019, 2021 and Microsoft 365.
AND: all conditions must be true
=AND(condition1, condition2, ...)
Returns TRUE only if every condition is true. Often used inside IF:
=IF(AND(B2>=33,C2>=33),"Pass","Fail")
This passes a student only if they scored 33 or more in both subjects.
OR: any condition is enough
=OR(condition1, condition2, ...)
Returns TRUE if at least one condition is true:
=IF(OR(B2>=90,C2>=90),"Distinction","Normal")
This gives "Distinction" if the student scored 90+ in either subject.
IFS: many outcomes, in order
IFS replaces a long nested IF. It checks conditions top to bottom and stops at the first TRUE one.
=IFS(B2>=75,"A", B2>=60,"B", B2>=33,"C", TRUE,"Fail")
Read it as: if 75+ then A, else if 60+ then B, else if 33+ then C, else Fail. The final TRUE,"Fail" is the catch-all "otherwise".
IFS vs nested IF
| Point | Nested IF | IFS |
|---|---|---|
| Readability | Hard with many levels | Cleaner, one function |
| Brackets | Many closing brackets | No nesting brackets |
| Version | All versions | Excel 2019/2021/365 |
| Catch-all | Final else value | TRUE, "value" at the end |
If you're on an older Excel without IFS, use nested IF instead.
Worked example (eligibility check)
A scholarship needs marks of 60+ AND attendance of 75%+. Marks in B, attendance in C:
=IF(AND(B2>=60,C2>=75),"Eligible","Not eligible")
If either condition fails, the student is "Not eligible". Swap AND for OR if only one condition needs to be met.
Pro tips
- Use AND when every rule must hold; use OR when any one rule is enough.
- End every IFS with
TRUE, "something"so unmatched rows don't return an error. - Order IFS conditions from highest to lowest (or most to least specific), since it stops at the first match.
Common mistakes
- Wrong order in IFS. If you put
B2>=33beforeB2>=75, everyone 33+ gets the first result. Go strict-to-loose. - No catch-all in IFS. Without a final
TRUE,..., unmatched values return#N/A. - Using AND when you meant OR. AND needs all conditions; OR needs any. Pick the right one.
Key takeaways
- AND is true only when all conditions are true.
- OR is true when any condition is true.
- IFS checks conditions in order and returns the first match.
- IFS (Excel 2019+) is cleaner than a long nested IF; end it with
TRUE, "...".
Practice task
Make a list of students with marks in two subjects. Write an AND formula that passes only those who cleared both, and an IFS formula that grades them A/B/C/Fail. Test a few rows to confirm the logic.
Go from basic formulas to real reporting logic in the ADCA program at HCI.
Frequently Asked Questions
What does the IFS function do in Excel?
IFS checks several conditions in order and returns the result for the first one that is true. It's a cleaner alternative to a long nested IF.
What is the difference between AND and OR in Excel?
AND returns TRUE only when every condition is true. OR returns TRUE when at least one condition is true.
How do I use IF with two conditions?
Combine IF with AND or OR: `=IF(AND(cond1,cond2),"Yes","No")` when both must be true, or use OR when any one is enough.
When should I use IFS instead of nested IF?
Use IFS when you have several outcomes and want cleaner, easier-to-read formulas. It needs Excel 2019, 2021 or Microsoft 365.
Why does my IFS formula show #N/A?
Because no condition matched and there's no catch-all. Add `TRUE, "value"` as the last pair to handle everything else.