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!

226 comments

  1. How to apply the IF OR formula for the 5 dysfunctions of a Team Assessment

    1. Hi! Sorry, I have no idea exactly what the task is. Have you tried any of the methods described in this blog post? If they don't work for you, please describe your problem in detail and I'll have a look at a possible solution.

  2. =If C2>99.99% than answer need 100% Ci, If D2>99.99% Than answer need 100% CO
    How can i apply this formula in excel.

  3. In an Excel file, if both the cells for in and out time data are blank, then a person is considered absent, and if either of the cells has an entry, then a person is considered present.

  4. How can i prepair an automated studentes roster including blind students?

  5. i used this and worked superb
    =IFERROR(IF(FIND(AE2,AF2)=1,"PROCESS ON LINK","PROCESS ON LINK"),"PROCESS MISSING ON LINK")

    PLANNE RING PROCESS Processes on the Link LINK CHECK
    1000 1000,240,526,530,1000,240,526,530 PROCESS ON LINK

    Look for the Planned ring Process Value in the Process on the Link and output Process ON LINK is found if not found print Process missing on Link
    challenge was with multiple appearance hence the double process on Link, check for appearing once and then more than once as well and if not found if its error u out put the process missing on link

    1. Hello Joel!
      If I understand the question correctly, in order to count duplicate words within a text string, you need to split that text string into individual words. This can be done using the TEXTSPLIT function. Then use the SUMPRODUCT function to count the number of matches. The CHOOSE function will return the text based on the number of matches. Based on your information, the formula might look something like this:

      =IFERROR(CHOOSE(SUMPRODUCT(--(TEXTSPLIT(AF2,,",")=(AE2&""))), "PROCESS ON LINK","Double","Triple"),"PROCESS MISSING ON LINK")

      You can also use a combination of the SUMPRODUCT and LEN functions in Excel. Click here to read a detailed description of how to count words: Count specific words / text in a cell. Based on the information given, the formula could be as follows:

      =IFERROR(CHOOSE(SUMPRODUCT((LEN(AF2)-LEN(SUBSTITUTE(AF2, AE2, "")))/LEN(AE2)), "PROCESS ON LINK","Double","Triple"),"PROCESS MISSING ON LINK")

  6. I am building a property model which assumes an acquisition date and an escalation date. I want to use Nested if statements to build an income statement, which should incorporate the escalation of the income streams on the escalation dates. Please propose the formula to use.

  7. Hi need some support.

    If cell v6 equal to 39 than 39 but is cell v6 is lower than 39 than need calculate v6-p5
    Also is if cell v6 is 39 than ignore calculation in cell p5

    2. Also what need to do if i have calculation in cell p5 but want 0 if v6 is 39

  8. I want to do this: if this, then this, or if this, then this, otherwise this
    Example, if A2=Male, enter "man", of if A2 = Female, enter "woman", otherwise enter "unknown"

  9. Hi there!
    I am trying to assign dates to my parts when they will be completed biased on what step they are in.
    a part has 9 different sequences it can be at and biased on what sequence it is at I can give an estimated date on when it will be completed.
    IE
    Part A is at sequence 50, it takes 5 days to complete it so it should be finished by 4-27-24.

    I had this working for if OR statements for the first two locations but as soon as I added a third (and I need to add 9 different location total) i get an error saying there are to many arguments. Any ideas on what I can do? Below is what should work but I get the 'to many arguments error"

    =IF(OR(E9="IPQA"), D8+H8, IF(OR(E9="PP1"), D8+I8), IF(OR(E9="PP2"), D8+j8), IF(OR(E9="MOLD"), D8+K8), IF(OR(E9="DEFLASH"), D8+L8), IF(OR(E9="ID"), D8+M8), IF(OR(E9="FINAL"), D8+N8), IF(OR(E9="FIN"), D8+O8))

    1. Hi! You don't need to use the OR function for one condition. Also, your formula has a lot of unnecessary parentheses. To write nested IF functions correctly, use these guidelines: Nested IF in Excel – formula with multiple conditions. You can also find useful information in this article: Use the new Excel IFS function instead of nested IF.
      Based on this information, the formula could be as follows:

      =IF(E9="IPQA",D8+H8,IF(E9="PP1",D8+I8,IF(E9="PP2",D8+J8,IF(E9="MOLD",D8+K8,IF(E9="DEFLASH",D8+L8,IF(E9="ID",D8+M8,IF(E9="FINAL",D8+N8,IF(E9="FIN",D8+O8))))))))

  10. Hello Sir,
    1. I have a bunch of car names in cell B20 be like ( Civic,Jazz,BRV,CRV ,Figo,Endover,Ecosport,XL-6,Breeza,X-Presso,Xcross, Baleno, Thar,Bolero)

    If B20 =any one value of (Civic,Jazz,BRV,CRV) then B21=Honda
    if B20=any one value of (Figo,Endover,Ecosport) then B21=Ford
    if B20=any one value of (XL-6,Breeza,X-Presso,Xcross, Baleno) then B21=Maruthi
    if B20=any one value of (Thar,Bolero) then B21=Mahindra
    I don't have the company names in my sheet so I need to compare the car name and give the car manufacturer detail in the next cell. Nearly i have 9000 so manually checing and updating is not possible. Please provide a query to accomplish it. Thanks in advance.

    1. Hello! You must split the text in cell B20 by each name to search for car names. Then use the MATCH function to find matches in the Vehicle Name list (column K) and extract the corresponding manufacturer from the corresponding cell in column L using INDEX function.

      =INDEX(L1:L10, INDEX(MATCH(TEXTSPLIT(B20,","),K1:K10,0), MATCH(TRUE,ISNUMBER(MATCH(TEXTSPLIT(B20,","),K1:K10,0)),0)))

      I hope my advice will help you solve your task.

      For more information, please visit: TEXTSPLIT function in Excel: split text strings by delimiter.

  11. Is there any other command, shortcut , other method for the following query in excel.

    =IF(OR(O2786="GITFASPRDSC1A",O2786="GITFASPRDSC10",O2786="GITBKPDEVSV01",O2786="GITBKPPRDSV01",O2786="GITFASPRDSC1B",O2786="GITNASPRDFS01",O2786="GITNASPRDFS02_DR",O2786="GITNASPRDFS03",O2786="GITNASSTGFS04_DR"),"GITFASPRDSC10")

    1. Hi! I can't understand your formula and check it as I don't have your data. I can't guess what result you wanted to get.

  12. I need help with generating a formula, please

    If cell H birthday is between 6/3/20 - 9/1/20 cell I is equal to TE; if cell H birthday is between 9/2/19 - 6/2/20 cell I is equal to TK; if cell H birthday is between 9/2/18 - 9/1/19 cell I is equal to K

  13. If want to output as below, which formula should I used?

    (98HVJM)HBRD2PT24KNOUV2PLG1K001H21 - Call
    (OGEFJC)RA996TH3QTP1CSG8AS34000QRF - Call
    (K0IHCC)IR6L50PF4HH077U6S3D80BK6UI - Call
    (0004Ka)JWWB8A1VR1 - Email
    else empty
    Showed above in bracket will not change

    1. Hi! Sorry, I have no idea exactly what the task is.
      It's hard to tell exactly what you're asking for as it's currently written.

    2. Currently I'm using like =IF(ISNUMBER(FIND("0004KaJ",A3)),"Email","Call")
      It will always output Call in column,
      wanted to have "" empty instead and only output when any of the above data input in A3

        1. Hi, yes, it works, thanks alot

  14. Column A has multiple ITEMS for eg - mango, banana, papaya, knife, spoons, garlic, ginger, tomato, potato, TV, Refrigerator, Microwave, Oven, Shirts, Pants, Shoes, Socks, Dell Laptop, Huawei Laptop, Lenovo Laptop, etc.
    Now in Column B, Types Of Items must be grouped -
    I need types of items =If(A1="mango","banana","papaya"),"FRUITS"),If( A1="knife", "spoons"),"Cutleries", if (A1="Garlic","potato","tomato,"Vegetables" and so on..

  15. Example not correct for the Belgian version:
    You have to use a ; between the statements in stead of '

  16. I've read through so many examples and still getting an error in my formula.

    I want to enter a value in a cell that depending on the range of that value then returns a specific price in another cell.

    If value is 24 or less, return a price of $15.
    If the value is between 25 and 34, return a price of $25
    And so on.

    How would you write this?

      1. I have two column both contain tru,false and blank...I want my answer to return one if both column are true what formula do i use?

      2. Thank you. I figured it out based on that article and wrote the following statement:
        =IF(C2>54,55, IF(C2>=45,45, IF(C2>=35,35, IF(C2>=25,25, IF(C2>=1,15)))))

        Now I need to add another test. If the value in B2 = No, then it's one set of pricing based on a range (as shown above), but if the value in B2 = Yes, then the pricing is different.

        1. Hi! If I understand your task correctly, try the following formula:

          =IF(B2="Yes", IF(C2>54,55, IF(C2>=45,45, IF(C2>=35,35, IF(C2>=25,25, IF(C2>=1,15))))), ..different pricing........ )

          There are detailed instructions in the article I've already recommended to you.

  17. how to write if condition in excel in a cell either for addition or subtraction or multiplication?

    For eg.:

    Customer Purchased Item ₹ 6,989 ₹ 6,989 ₹ 6,989
    Customer Purchased Item ₹ 40,000 ₹ 46,989 ₹ 46,989
    Customer Purchased Item ₹ 28,541 ₹ 75,530 ₹ 75,530
    Cash Received from customer ₹ 35,000 ₹ 40,530 ₹ 40,530
    Customer paid chq #: 39759 ₹ 39,759 ₹ 771 ₹ 771

    1. No one will be able to guess what you want to do with your data. To learn how to write an IF condition, see the recommendations in the comment above.

  18. how to write if condition in excel in a cell either for addition or subtraction or multiplication?

    1. Hi Jeevan,

      if you would like to write a if condition in excel that either for addition or subtraction or multiplication, you must be prepare with the logic what you want to write.

  19. Hello. I have a construction bid spreadsheet that incorporates a tiering labor cost, dependent upon the square footage of material. Example: 1-30sf is $225 in labor, 31-59sf is $337.50, 60sf+ is $450. My working formula to calculate the material cost and labor is below:
    =IFS(B24=60,SUM(C$14*B24)+SUM(C$15*B24)+C$18,B24>=31,SUM(C$14*B24)+SUM(C$15*B24)+C$17,B24>=1,SUM(C$14*B24)+SUM(C$15*B24)+C$16)

    I am now adding a flat labor rate to install the material in a specific pattern. This flat rate would override the tiering labor rates above if using the pattern. My thought is to add a "yes"/"no" dropdown in cell D21 to select if the material will be installed in a pattern, with the flat labor rate in C21. The square footage of the job in in cell B24 of the formula below.

    What formula would I use to use the above formula if D21 is "NO", but if D21 is "YES" use a different formula such as B24=SUM(C$14*B24)+SUM(C$15*B24)+C21?

    I continue to get errors...

    1. Hi! Use the IF function to do calculations depending on the value of B21:

      =IF(B21="Yes", SUM(C$14*B24)+SUM(C$15*B24)+C21, IFS(B24=60,SUM(C$14*B24)+SUM(C$15*B24)+C$18,B24>=31, SUM(C$14*B24)+SUM(C$15*B24)+C$17,B24>=1, SUM(C$14*B24)+SUM(C$15*B24)+C$16))

      Hope this is what you need.

  20. Hi,
    I need one formula but not able to figure out how to apply. I have two cells. in cell 1 entries may be(Yes, No) in cell 2 entries may be (Required, Not required), now the conditions I want to check. if in cell 1 "Yes" and Cell 2 "Required" then output should be "Yes". But if in cell 1 "Yes" and in Cell 2 "Not Required" then the answer should be "No".

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 :)