Nested IF with AND and OR
AND requires every test to be true, OR requires at least one, and IF turns the combined result into a label.
=IF(AND(B2>=50,OR(C2="Yes",D2>=3)),"Approve","Review")The sheet: Column B is a score, column C is Referred (Yes or No) and column D is years of experience. A row is approved when the score is at least 50 and the person was referred or has 3 or more years.
How it works, step by step
- 01B2>=50 must be true for the row to be approved.
- 02OR(C2="Yes",D2>=3) is true if the person was referred or has at least 3 years of experience.
- 03AND combines the two: the score test and the OR test must both hold.
- 04IF returns Approve when AND is true, otherwise Review.
The same logic in four tools
=IF(AND(B2>=50,OR(C2="Yes",D2>=3)),"Approve","Review")=IF(AND(B2>=50,OR(C2="Yes",D2>=3)),"Approve","Review")SELECT *,
CASE WHEN score >= 50 AND (referred = 'Yes' OR years >= 3)
THEN 'Approve' ELSE 'Review' END AS decision
FROM candidates;import numpy as np
df["decision"] = np.where(
(df["score"] >= 50) & ((df["referred"] == "Yes") | (df["years"] >= 3)),
"Approve",
"Review",
)Mistakes that break it
- !A blank cell counts as 0 in a comparison, so a blank score fails B2>=50 rather than raising an error.
- !Text comparisons such as C2="Yes" ignore capitalization in Excel and Sheets but SQL (depending on the collation) and pandas can be case sensitive.
- !In pandas use & and | with parentheses around each comparison, not and or or.
Common questions
- Do I need nested IFs for this?
- Not when the outcome is one of two labels. AND and OR inside a single IF are easier to read than IF inside IF.
- How many IFs can I nest?
- Excel allows 64 levels, but beyond three IFS or a lookup table is easier to maintain.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.