Cellwise Try it free

Excel guide · 3 min read

How to use IF with AND and OR in Excel

Quick answerPut AND or OR in the first argument of IF. =IF(AND(B2>=650,C2>=40000),"Approve","Decline") approves only when both tests pass. =IF(OR(B2>=750,D2="Yes"),"Fast track","Normal") fast-tracks when either test passes. NOT reverses a test.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1ApplicantCreditIncomeExisting
2R. Alvarez70258000No
3K. Osei64172000Yes
4M. Chen78039000No
5S. Patel61531000No
6J. Novak69045500Yes

IF with AND: every test must pass

AND(test1, test2, ...) returns TRUE only if all of its tests are TRUE. Here an applicant is approved with a credit score of at least 650 and income of at least $40,000.

K. Osei has the income but a 641 score, so AND is FALSE and the result is Decline.

=IF(AND(B2>=650,C2>=40000),"Approve","Decline")

Result: Approve

R. Alvarez has 702 and 58,000. Both tests pass, so the result is Approve.

IF with OR: one passing test is enough

OR(test1, test2, ...) returns TRUE if any test is TRUE. Use it for "either of these qualifies" rules.

Existing customers or anyone with a 750+ score get fast-tracked. M. Chen qualifies on score, K. Osei on being an existing customer.

=IF(OR(B4>=750,D4="Yes"),"Fast track","Normal")

Result: Fast track

M. Chen has a 780 score, so OR is TRUE even though D4 is "No".

Check a value is between two numbers

A range check is just AND with a lower and upper bound. You cannot write 650<=B2<=700 the way you would on paper.

Excel would evaluate 650<=B2 first, get TRUE, then compare TRUE with 700. In Excel comparisons, TRUE counts as larger than any number, so that formula is always FALSE.

=IF(AND(B6>=650,B6<=700),"Near prime","Other")

Result: Near prime

J. Novak's 690 is between 650 and 700, so the result is Near prime.

Combine AND and OR

Real rules often mix both. Approve if the score is 650+ and either the income is 40,000+ or the applicant is an existing customer. The OR sits inside the AND as one of its tests.

=IF(AND(B3>=650,OR(C3>=40000,D3="Yes")),"Approve","Decline")

Result: Decline

K. Osei fails the 650 score test, so AND is FALSE regardless of the OR, and the result is Decline.

Reverse a test with NOT

NOT flips TRUE to FALSE. It reads well for "anything except" rules. You can also write D5<>"Yes", which means "not equal to".

=IF(NOT(D5="Yes"),"Send welcome pack","-")

Result: Send welcome pack

S. Patel is not an existing customer, so NOT(FALSE) is TRUE and the result is Send welcome pack.

Test the logic before wrapping it in IF

Long AND and OR formulas are easier to debug in pieces. Type just the AND or OR part, such as =AND(B3>=650,OR(C3>=40000,D3="Yes")), into a spare cell and check it says TRUE or FALSE as you expect for a few rows. Then wrap it in IF.

You can also click inside the formula and use Formulas > Evaluate Formula to step through each test and see which one turns the result FALSE.

Common mistakes

Writing =IF(D2="Yes" OR "Maybe", ...)

Each test must be complete: OR(D2="Yes",D2="Maybe"). Excel does not carry the D2= over to the second value.

Using AND when you mean OR

AND(D2="Yes",D2="No") can never be TRUE, because one cell cannot hold both values. Read the rule aloud: "either" means OR.

Comparing numbers stored as text

An imported "702" is text, and text compares as larger than any number. Check with ISNUMBER and convert with VALUE or Data > Text to Columns.

FAQ

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. Both return a single TRUE or FALSE that IF can use.

Can I use AND without IF?

Yes. =AND(B2>=650,C2>=40000) on its own shows TRUE or FALSE. That is handy as a helper column or in a conditional formatting rule.

Is there an XOR function?

Yes, Excel 2013 and later have XOR, which returns TRUE when an odd number of the conditions are TRUE. For two conditions, that means exactly one of them.

Functions used

Related guides

Updated