Excel IF OR function with formula examples

The tutorial shows how to write an IF OR statement in Excel to check for various "this OR that" conditions.

IF is one of the most popular Excel functions and very useful on its own. Combined with the logical functions such as AND, OR, and NOT, the IF function has even more value because it allows testing multiple conditions in desired combinations. In this tutorial, we will focus on using IF-and-OR formula in Excel.

IF OR statement in Excel

To evaluate two or more conditions and return one result if any of the conditions is TRUE, and another result if all the conditions are FALSE, embed the OR function in the logical test of IF:

IF(OR(condition1, condition2,...), value_if_true, value_if_false)

In plain English, the formula's logic can be formulated as follows: If a cell is "this" OR "that", take one action, if not then do something else.

Here's is an example of the IF OR formula in the simplest form:

=IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

What the formula says is this: If cell B2 contains "delivered" or "paid", mark the order as "Closed", otherwise "Open".
IF OR statement in Excel

In case you want to return nothing if the logical test evaluates to FALSE, include an empty string ("") in the last argument:

=IF(OR(B2="delivered", B2="paid"), "Closed", "")

The same formula can also be written in a more compact form using an array constant:

=IF(OR(B2={"delivered","paid"}), "Closed", "")
IF OR formula to return nothing if none of the conditions is met.

In case the last argument is omitted, the formula will display FALSE when none of the conditions is met.

Note. Please pay attention that an IF OR formula in Excel does not differentiate between lowercase and uppercase characters because the OR function is case-insensitive. In our case, "delivered", "Delivered", and "DELIVERED", are all deemed the same word. If you'd like to distinguish text case, wrap each argument of the OR function into EXACT as shown in this example.

Excel IF OR formula examples

Below you will find a few more examples of using Excel IF and OR functions together that will give you more ideas about what kind of logical tests you could run.

Formula 1. IF with multiple OR conditions

There is no specific limit to the number of OR conditions embedded into an IF formula as long as it is in compliance with the general limitations of Excel:

  • In Excel 2007 and higher, up to 255 arguments are allowed, with a total length not exceeding 8,192 characters.
  • In Excel 2003 and lower, you can use up to 30 arguments, and a total length shall not exceed 1,024 characters.

As an example, let's check columns A, B and C for blank cells, and return "Incomplete" if at least one of the 3 cells is blank. The task can be accomplished with the following IF OR function:

=IF(OR(A2="",B2="", C2=""),"Incomplete","")

And the result will look similar to this:
IF with multiple OR conditions

Formula 2. If a cell is this OR that, then calculate

Looking for a formula that can do something more complex than return a predefined text? Just nest another function or arithmetic equation in the value_if_true and/or value_if_false arguments of IF.

Say, you calculate the total amount for an order (Qty. multiplied by Unit price) and you want to apply the 10% discount if either of these conditions is met:

  • in B2 is greater than or equal to 10, or
  • Unit Price in C2 is greater than or equal to $5.

So, you use the OR function to check both conditions, and if the result is TRUE, decrease the total amount by 10% (B2*C2*0.9), otherwise return the full price (B2*C2):

=IF(OR(B2>=10, C2>=5), B2*C2*0.9, B2*C2)

Additionally, you could use the below formula to explicitly indicate the discounted orders:

=IF(OR(B2>=10, C2>=5),"Yes", "No")

The screenshot below shows both formulas in action:
If a cell is this OR that, then calculate

Formula 3. Case-sensitive IF OR formula

As already mentioned, the Excel OR function is case-insensitive by nature. However, your data might be case-sensitive and so you'd want to run case-sensitive OR tests. In this case, perform each individual logical test inside the EXACT function and nest those functions into the OR statement.

IF(OR(EXACT(cell,"condition1"), EXACT(cell,"condition2")), value_if_true, value_if_false)

In this example, let's find and mark the order IDs "AA-1" and "BB-1":

=IF(OR(EXACT(A2, "AA-1"), EXACT(A2, "BB-1")), "x", "")

As the result, only two orders IDs where the letters are all capital are marked with "x"; similar IDs such as "aa-1" or "Bb-1" are not flagged:
Case-sensitive IF OR formula

Formula 4. Nested IF OR statements in Excel

In situations when you want to test a few sets of OR criteria and return different values depending on the results of those tests, write an individual IF formula for each set of "this OR that" criteria, and nest those IF's into each other.

To demonstrate the concept, let's check the item names in column A and return "Fruit" for Apple or Orange and "Vegetable" for Tomato or Cucumber:

=IF(OR(A2="apple", A2="orange"), "Fruit", IF(OR(A2="tomato", A2="cucumber"), "Vegetable", ""))
Nested IF OR statement

For more information, please see Nested IF with OR/AND conditions.

Formula 5. IF AND OR statement

To evaluate various combinations of different conditions, you can do AND as well as OR logical tests within a single formula.

As an example, we are going to flag rows where the item in column A is either Apple or Orange and the quantity in column B is greater than 10:

=IF(AND(OR(A2="apple",A2="orange"), B2>10), "x", "")
IF AND OR to test various combinations of multiple conditions

For more information, please see Excel IF with multiple AND/OR conditions.

That's how you use IF and OR functions together. To have a closer look at the formulas discussed in this short tutorial, you are welcome to download our sample Excel IF OR workbook. I thank you for reading and hope to see you on our blog next week!

Table of contents

216 comments

  1. Your examples helped me find a solution - thanks for posting this page.

    Please check to see if the following is an error in the section "IF OR statement in Excel" where you state the lines below [in brackets like those enclosing this phrase to avoid confusion if I used double quotes]:

    [ Here's is an example of the IF OR formula in the simplest form:

    =IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

    What the formula says is this: If cell B2 contains "delivered" or "cancelled", mark the order as "Closed", otherwise "Open". ]

    However, as I read the formula, it indicates that if cell B2 contains "delivered" or "paid" (not "cancelled") then the order will be marked as "Closed". If you look at the screen shot, the row containing "Cancelled" shows a Status of "Open", not "Closed" as your explanation states it will. Please clarify for your readers.

    • Hello!

      Of course, it is "paid", not "cancelled". Thank you for pointing that out, fixed!

  2. I am trying to say that if One Cell = this amount add / subtract a Certain amount.

    Can this be done??

    EX: =IF(D6/7=E6,G6) OR (D6/7=E6,H6) OR (D6/7=E6,I6) OR (D6/7=E6,J6) OR (D6/7=E6,K6)

    Thx...Dawn

  3. =IF(AND(A2="VISHAL", B2="HP", C2=610), "6", "10"), IF(AND(A2="VISHAL", B2="HP", C2=2310), "15", "20")

    WILL THIS WORK?????????
    I NEED TO ENTER MULTIPLE RESULT IN A SINGLE CELL, FROM DIFFERENT CONDITIONS.

    • Hello!
      Your conditions contradict each other. For example, if A2 = ”VISHAL”, B2 = ”HP”, C2 = 900 then the first condition will return 10, and the second - 20.

  4. This isn't working. What am I doing incorrectly?

    =IF((OR(E2=Daily, E2=Weekly)), Next Shift, ENTER DATE)

    Column E indicates if a project is due daily or weekly. Column F would ideally calculate today+1 for daily or today+8 days for weekly.

    • Hi,
      You must enclose text values in quotation marks, such as "Weekly".
      What is "Next Shift, ENTER DATE"?
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(E2="Daily",TODAY()+1,IF(E2="Weekly",TODAY()+8,""))

      Hope this is what you need.

  5. I got it! thank you. :)

  6. Hi.. need help.
    i have date today and start date, to calculate the case age but another column is the status of the case, close or open.. so the logic will be.. calculate the case age if the case is still open..

    thank you in advance.

    • I got this formula: =IF(OR(C2="Closed","--"),(SUM(A2-B2)))
      but..
      it's working but the other way around. it calculates the age if the case is marked as "Closed".

  7. Hi, I would like to know a formula to show if something if greater than or less than a number to show a figure for example

    11 years service - if the years service is more than 10 to show 2, if it is less than 10 but more than 5 to show 1 and if it is less than 5 to show 0.

    hope this makes sense.

    TIA

    • Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =IF(A1>10,2,IF(A1>5,1,0))

      • there are some proble with me in excell example
        =if(a1<10,100,"enough",if(d1=0,"niu"))

  8. Please I need your help how can I come up with the formula for this
    45000 =0%
    5000=15%
    Next 2950000=30%
    Excess 3000000=35%

  9. I need some help in constructing the formula to this:
    I need to derive a result(column title) if ALW(column title) is 1.56 and up its Oversize, if ALW is 1.20-1.55 its Goodsize, if ALW is 1.10-1.19 its Undersize, if ALW is 1.0-1.09 its Offsize, and if ALW is below 1.0 its Runts

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      What is the column title? In Excel and other spreadsheet applications, the column header is the colored row of letters used to identify each columnwithin the sheet, or workbook. Column title is a letter.
      If your question is about an Excel cell -

      =IF(A1>=1.56,"Oversize", IF(A1>=1.2,"Goodsize", IF(A1>=1.1,"Undersize", IF(A1>=1,"Offsize", "Runts" ))))

  10. i need a formula like ( date of joinin - current date less than 365 days then the answer should be 0

  11. Hi,
    I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
    =IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE)))))

    • Hi,
      I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
      =IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE))))).
      Thanks so much.

    • Hello Joanne!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  12. IF(A1="DELIVERY",THEN C1(CELL NO)*.020%,IF NO C1*.004% I NEED CORRECT FORMULA

  13. How do I write the formula for... If either Cell A1 or Cell D1 contains a term, say "ENGLISH", then the consequent grade of ENGLISH from the C1 or F1 should be filled in cell G1.
    Is it possible?

    • Hello Michael!
      If I got you right, the formula below will help you with your task:

      =IF(A1="ENGLISH",C1, IF(D1="ENGLISH",F1,""))

      I hope it’ll be helpful.

  14. =IF(E45="PA1",0.85,IF(E45="PA2",0.95,IF(E45="CB1",0.99,"")))

  15. I NEED A FORMULA FOR CELL F45
    IF CELL E45 = "PA1" THEN CELL F45= .85 AND IF CELL E45 = PA2 THEN CELL F45 READS .95 AND IF CELL = E45 - CB1 THEN F45 = .99

    THANKS

  16. Trying to write a formula that picks out the word grapefruit from D14 or the word recorder from D14 and gives a zero.. if those words aren't found then F14-E14. The formula works for just Grapefruit but when I add in the Or and Recorder it doesn't. What am I writing wrong? It's telling me to many arguments.
    =If(Or(Is number(Search("Grapefruit"'D14,(Is number(Search("Recorder",D14),0,F14-E14))

  17. Can I not make cell to cell comparison with if/or? Here is the formula I am using.
    =IF(OR(D3<E3,D3=E3,"Good", "Review"))

    • Here is the formula I used after reviewing the responses to other questions on this forum. New formula works.
      =IF(D3<=E3, "Good", "Review")

      • Hi Sam,
        Your original formula would work as well. You just had to move the other bracket to close off the or( function.
        =IF(OR(D3<E3,D3=E3),"Good", "Review")

  18. Can you help me on the error in this formula. =IF(ISNUMBER($AH15),ANDIF($AH15>150,(" High Random Blood Sugar "&$AH15&" Mg.%. ", "")&" "&IF($AH15>150,"Urine Sugar "&$AI15&". ", ""),("")
    AH15 is Number or Text "ND" i.e. Not Done.
    Thank you.

    • =IF(OR(ISNUMBER($AH15),$AH15="ND"),IF($AH15>150,"High Random Blood Sugar"&$AH15&"Mg.%.","")&" "&IF($AH15>150,"Urine Sugar"&" "&$AI15&".",""),"")
      The above formula seems to work for me.

  19. I have a price range for warranty coverage. I need to see when sales either sold the item over or under the range for a warranty package. For example:
    Min Product $ Range Max Product $ Range Product $ Sold
    1000 1499.99 269.00
    300 599.99 1049.00
    1000 1499.99 578.00
    600 799.99 1456.00

    I need a formula that tells me if the product sold for $269.00 was "oversold" or "undersold" contract range? I tried =if(or(c1=B2,"oversold"))
    It doesn't work. What am I doing wrong?

    • A = Min / B = Max / C = Sold
      =IF(C1B1,"OVERSOLD","")

      • Something is wrong with the formula not being posted properly.
        =IF(C1B1,"OVERSOLD","")
        It should be:
        =IF(C1 less than A1,"UNDERSOLD",IF(C1 greater than B1,"OVERSOLD","")

  20. Hello,
    I want to write a formula to write C1 as:
    1 if A1>10 or B1>20
    2 if 7<A1<10 or 10<B1<20
    3 if 4<A1<7 or 5<B1<10
    4 if 1<A1<4 or 1<B1<5
    5 if A1<1 or B1<1

    Thanks in advance

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)