SheetSense

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.

fx
=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

  1. 01B2>=50 must be true for the row to be approved.
  2. 02OR(C2="Yes",D2>=3) is true if the person was referred or has at least 3 years of experience.
  3. 03AND combines the two: the score test and the OR test must both hold.
  4. 04IF returns Approve when AND is true, otherwise Review.

The same logic in four tools

Excel
=IF(AND(B2>=50,OR(C2="Yes",D2>=3)),"Approve","Review")
Google Sheets
=IF(AND(B2>=50,OR(C2="Yes",D2>=3)),"Approve","Review")
SQL
SELECT *,
       CASE WHEN score >= 50 AND (referred = 'Yes' OR years >= 3)
            THEN 'Approve' ELSE 'Review' END AS decision
FROM candidates;
Python (pandas)
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.