IF with OR in Excel: Pass When Any Condition Is Met

An IF or formula in Excel returns one result when at least one of several conditions is true. You put OR inside IF: =IF(OR(test1, test2), "Yes", "No"). If any test passes, you get the first result; only when they all fail do you get the second. This guide builds the formula on a shipping table, then covers checking one cell against several values, combining OR with AND, and the mistakes that cause errors.

How OR works on its own#

OR takes a list of conditions and returns TRUE if any one of them is true. It returns FALSE only when every condition is false.

=OR(logical1, [logical2], ...)

Compare this with AND, which needs every condition to be true. OR accepts up to 255 conditions. Excel also has XOR, an "exclusive or" that returns TRUE when an odd number of conditions are true; with two conditions, that means exactly one of them. It's rarely needed, but it's handy for rules like "one discount or the other, not both". The lesson on AND, OR and NOT lets you practise all three, and the OR function guide is a quick reference.

How to write IF with OR, step by step#

The examples use this order table in A1:C6. An order ships free if the customer is a VIP or the order is worth 500 or more:

1. Click D2 and type =IF(OR(.
2. Type the first condition, B2="VIP", then a comma.
3. Type the second condition, C2>=500, and close the OR bracket.
4. Add the results, "Free shipping","Standard", and close the IF bracket.

=IF(OR(B2="VIP",C2>=500),"Free shipping","Standard")

Fill down to D6 (Windows: Ctrl+D; Mac: ⌘D). A101 and A104 are free because the customer is a VIP, even though A104 is only worth 40. A102 is free because it's worth 650. A103 and A105 fail both tests and show Standard. A105 is worth 499, just under the limit, which is why >=500 and not >500 matters at the edges.

How to check if a cell equals one of several values#

Repeat the cell reference in every condition. Each part of OR must be a complete test.

=IF(OR(B2="North",B2="South"),"Southern team","Other")

A very common mistake is to write OR(B2="North","South"). The second part is just the word South, not a comparison, so OR can't read it as TRUE or FALSE, and the formula returns #VALUE!.

For a long list of values, a shorter form uses an array constant in curly brackets:

=IF(OR(B2={"North","South","East"}),"Yes","No")

Excel compares B2 with each value in the list, and OR returns TRUE if any comparison matches. You type the curly brackets yourself; this works in all current versions of Excel. For checking whether a cell contains a word, rather than equals it, see IF with text.

How to use IF OR with numbers#

OR is handy for flagging values outside a safe range: too low or too high.

=IF(OR(C2<100,C2>1000),"Check","OK")

Only values under 100 or over 1,000 are flagged. On the order table, A104 (40) and A103 (80) show Check, while A101, A102 and A105 show OK. This is the mirror image of a "between" test, which uses AND; see IF between two numbers.

How to combine OR with AND#

Nest them when a rule has both "any of" and "all of" parts. Say an order ships free if the customer is a VIP, or if it's worth 500 or more and already paid (column E):

=IF(OR(B2="VIP",AND(C2>=500,E2="Paid")),"Free shipping","Standard")

Read it from the inside out. AND is TRUE only for big, paid orders. OR is TRUE if the customer is a VIP or that AND is true. The brackets decide which conditions belong together, so check them carefully. For the AND side on its own, read IF with AND.

Using NOT to flip a condition#

NOT turns TRUE into FALSE and FALSE into TRUE. It's useful when "none of these" is easier to say than "any of these".

=IF(NOT(OR(B2="VIP",C2>=500)),"Charge shipping","")

This shows Charge shipping only when the customer is not a VIP and the order is under 500. It's the same as =IF(AND(B2<>"VIP",C2<500),"Charge shipping",""). Use whichever reads more clearly.

How to count rows that meet any condition#

COUNTIFS works with AND logic only. For OR logic, add separate COUNTIFs together:

=COUNTIF(B2:B6,"VIP")+COUNTIF(C2:C6,">=500")

This gives 2 + 1 = 3. Be careful: if a VIP also had an order of 500 or more, that row would be counted twice. When the conditions can overlap, add a helper column with the IF OR formula and count its results with =COUNTIF(D2:D6,"Free shipping") instead.

Common IF OR mistakes#

Check these when the formula misbehaves:

1. Incomplete conditions. OR(B2="North","South") fails. Write OR(B2="North",B2="South").
2. OR when you need AND. If every condition must be true, use AND.
3. Brackets closed too late. Close OR before the true and false results: IF(OR(a,b),"Yes","No").
4. Text without quotes. B2=VIP returns #NAME?. Write B2="VIP".
5. Blank cells. A blank cell can make a "less than" test TRUE, because Excel treats it as 0. Check for blanks first; see IF cell is blank.

The IF function guide and the IF function explained simply cover the basics if you need a refresher.

Practise it#

IF with OR appears in most Excel screening tests, often right next to IF with AND to check you know the difference. Practise AND, OR and NOT in a live spreadsheet: you type the formula and it checks it instantly. Then practise the IF function, and try the intermediate Excel test when you're ready.