How to Build an IF Formula with AND and OR in Excel

Decide whether the rule needs AND or OR

Before writing an IF formula, translate the business rule into plain language. Use AND when every condition must be true. Use OR when any one of several conditions is enough to trigger the result. The IF function then turns that TRUE or FALSE test into the label or value you want to return.

Assume Amount is in B2, Status is in C2, and Approval is in D2. If a record should be reviewed only when the amount is at least 1,000 and the status is Open, use:

=IF(AND(B2>=1000,C2="Open"),"Review","No review")

If action is needed when the status is Overdue or the approval is Escalated, use:

=IF(OR(C2="Overdue",D2="Escalated"),"Action needed","Monitor")

Read the formula from the inside out

In the first formula, Excel evaluates AND(B2>=1000,C2="Open") first. AND returns TRUE only when both tests are true. IF sees that TRUE result and returns Review. If either test is false, AND returns FALSE and IF returns No review.

The OR formula works differently. OR(C2="Overdue",D2="Escalated") returns TRUE when either test is true, including when both are true. IF then returns Action needed.

Condition A	Condition B	AND	OR
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE

Worked example with real outcomes

Consider four records:

Amount	Status	Approval	AND result	OR result
1,250	Open	Normal	Review	Monitor
800	Open	Escalated	No review	Action needed
1,500	Overdue	Normal	No review	Action needed
600	Closed	Normal	No review	Monitor

The first row meets both AND conditions because 1,250 is at least 1,000 and Status is Open. The second row fails the amount test, so AND returns No review, but Approval is Escalated, so OR returns Action needed. The third row has a large amount but Status is Overdue rather than Open; it fails the AND rule but satisfies the OR rule.

Combine AND and OR when the rule has two levels

Some business rules contain both required conditions and acceptable alternatives. For example: review the record when Amount is at least 1,000 and the Status is either Open or Overdue.

=IF(AND(B2>=1000,OR(C2="Open",C2="Overdue")),"Review","No review")

Here, OR handles the acceptable status alternatives, while AND requires both the amount threshold and one of those statuses. Parentheses show which tests belong together, so build and test the inner logical functions before adding IF.

Put thresholds and labels in cells when they may change

Hard-coding 1000 is fine for a fixed example, but a real workbook is easier to maintain when changeable thresholds live in labeled setup cells. If the review threshold is in H2, use an absolute reference when copying the formula:

=IF(AND(B2>=$H$2,C2="Open"),"Review","No review")

This makes the rule visible to users and avoids editing dozens of formulas when the threshold changes.

Handle blanks deliberately

A formula can classify an incomplete row unless you tell it what to do with missing inputs. If Amount and Status are required before the rule should run, add a blank check first:

=IF(OR(B2="",C2=""),"",IF(AND(B2>=1000,C2="Open"),"Review","No review"))

The outer IF leaves the result blank until both required inputs exist. Whether that is the right behavior depends on the workbook, but the decision should be explicit rather than accidental.

Watch for text inconsistencies

Logical formulas depend on the values they receive. A status list that contains Open, OPEN, trailing spaces, or misspellings can produce unexpected results. Data Validation is a good way to keep status and approval values consistent. If imported text may contain extra spaces, clean it before relying on the logical test.

Common IF, AND, and OR mistakes

Mistake	Why it matters
Using AND for acceptable alternatives	The formula becomes too strict because every alternative would need to be true.
Using OR when all requirements are mandatory	Records can pass after meeting only one condition.
Building a long formula before testing each test	It becomes harder to find which condition is wrong.
Ignoring blanks	Incomplete records can receive a real classification.
Hard-coding changeable thresholds everywhere	Future rule changes require editing many formulas.
Returning inconsistent labels	Filtering and summary reports become harder to maintain.

Test the rule at the boundaries

Do not test only obvious examples. For the 1,000 threshold, test 999, 1,000, and 1,001. Test a blank Amount, a blank Status, one row where only the first AND condition is true, and one row where only the second OR condition is true. This confirms that the comparison operators and logical grouping match the written rule.

Frequently asked questions

Can AND and OR be used in the same IF formula?

Yes. Nest them according to the business rule. Use parentheses to make the required groups clear and test the inner logical functions independently.

Should I use nested IF instead?

Use nested IF only when the result genuinely depends on several ordered decisions. For one TRUE/FALSE rule with several conditions, IF combined with AND or OR is usually easier to audit.

Why does a text condition appear to fail?

Check the actual source value for misspellings, extra spaces, and formulas returning empty strings. A controlled drop-down can prevent many status-text problems.

Can the formula return numbers instead of labels?

Yes. The value-if-true and value-if-false arguments can return numbers, cell references, calculations, dates, or text, depending on the result your workbook needs.