Excel AND and OR Inside IF Formulas
By Szabó Gergő · Updated
AND requires every test to be true. OR requires at least one. Use them inside IF, or multiply and add Boolean arrays.
Syntax and arguments
=IF(AND(test1, test2), value_if_true, value_if_false)- test1
- A TRUE/FALSE expression.
- test2
- Another test. AND needs all; OR needs one.
AND / OR examples
01
Both tests
Status B2, amount C2.
=IF(AND(B2="Paid",C2>=100),"OK","Check")Both the status and the amount must pass.
02
Either test
Rush flag in D2.
=IF(OR(B2="Rush",C2>=5000),"Priority","Normal")One true test is enough.
03
Array AND
FILTER with two tests.
=FILTER(A2:C100,(B2:B100="Paid")*(C2:C100>=100))TRUE*TRUE is 1. This is the array form of AND.
Common mistakes
Writing AND(B2:B100="Paid")
AND on a range returns one result, not a per-row array. Use * in FILTER or SUMPRODUCT.
OR of many IFs
One OR list is clearer than nested IF(OR(...)).
Comparing to TRUE as text
Use TRUE, not "TRUE", unless the cell actually stores the word.
AND / OR FAQ
What is XOR?
XOR is true when an odd number of tests are true. Rare in business sheets.
Can I nest AND in OR?
Yes: =IF(OR(AND(A2="East",B2="Paid"),C2="VIP"),"Yes","No").
Does IFS replace AND?
IFS is a chain of exclusive tests. AND still belongs inside one of those tests.