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. Hi,
    Helpfull article!

    I have 6 variables in total. Only one variable will actually be found each time and then I would like that specific variable back in text.
    *have to use ";" instead of comma's in my excel.
    Doing this now, but not working:

    =IF(OR(ISNUMBER(SEARCH("Var1";A28));"Var1");
    IF(OR(ISNUMBER(SEARCH("Var2";A28));"Var2");
    IF... etc. ))

    • Hello!
      You can use nested IF formula

      =IF(ISNUMBER(SEARCH("Var1",A28)),"Var1",IF(ISNUMBER(SEARCH("Var2",A28)),"Var2",IF(ISNUMBER(SEARCH("Var3",A28)),"Var3","")))

      If you have many conditions try using the IFS function instead of multiple IF:

      =IFS(ISNUMBER(SEARCH("Var1",A28)),"Var1",ISNUMBER(SEARCH("Var2",A28)),"Var2",ISNUMBER(SEARCH("Var3",A28)),"Var3")

      I hope my advice will help you solve your task.

  2. Hi need help. I would want to automatically get the rates when these combinations are selected. Please see table. Thank you so much in advance.

    Service Paper Size Print Color Rate
    Print - Plain TEXT Letter Grayscale | B/W 5.00
    Print - Plain TEXT A4 Grayscale | B/W 5.00
    Print - Plain TEXT Long / Folio Grayscale | B/W 7.00
    Print - IMAGE (Half page) Letter Grayscale | B/W 7.00
    Print - IMAGE (Half page) A4 Grayscale | B/W 7.00
    Print - IMAGE (Half page) Long / Folio Grayscale | B/W 10.00
    Print - IMAGE (Half page) Letter Colored 12.00
    Print - IMAGE (Half page) A4 Colored 12.00
    Print - IMAGE (Half page) Long / Folio Colored 15.00
    Print - IMAGE (Full page) Letter Grayscale | B/W 10.00
    Print - IMAGE (Full page) A4 Grayscale | B/W 10.00
    Print - IMAGE (Full page) Long / Folio Grayscale | B/W 12.00
    Print - IMAGE (Full page) Letter Colored 15.00
    Print - IMAGE (Full page) A4 Colored 15.00
    Print - IMAGE (Full page) Long / Folio Colored 20.00
    Print - Digital photo 4R Colored 30.00
    Photocopy Letter Grayscale | B/W 5.00
    Photocopy Letter Colored 7.00
    Photocopy A4 Grayscale | B/W 5.00
    Photocopy A4 Colored 7.00
    Scan 10.00
    addt'l - Editing 3.00

      • Thank you for this but I am not looking for the delimiter. What I am trying to get is the "RATES". For instance if I input "Print - Plain TEXT" on "SERVICE" then "Long / Folio" on the "PAPER SIZE" then "Grayscale" on "Print colour" it will get me automatically the "RATE" of 7.00..

          • Hey Alexander Trifuntov ! Thank you so much for the help. Works really great! Awesome! Just as the result I really wanted. :D

  3. I am trying to sum a range of cells if another range of cells says either yes or no. If yes then sum the cells, if no, then subtract the amount in that cell. Can someone help?

  4. doc_no frm_date to_date missing date
    1662450337 01-Apr-22 04-Apr-22
    1662450337 05-Apr-22 07-May-22
    1662450337 08-May-22 04-Jun-22
    1662450337 05-Jun-22 04-Jul-22
    1662450337 05-Jul-22 04-Aug-22
    1662450337 05-Aug-22 04-Sep-22
    1662450337 05-Sep-22 04-Oct-22

  5. Hi,

    Can you please help. i need help with the following

    =IF(OR(K20="DE",K20="FR",K20="SE",K20="ES",K20="IE",K20="IT",K20="DK",K20="NL",Z20>150),"GB 320000",K20)

    if for example K20= "CH" is not listed in the above formula. is there an add on to this formula to just show K20 as CH

    Hope this makes sense.

    thanks

    • this is the full formula i'm looking for, but no joy. help would be greatly appreciated

      =IF($K20="DE",IF($Z20>150,GB 320000),IF($K20="FR",IF($Z20>150,GB 320000),IF($K20="SE",IF($Z20>150,GB 320000),IF($K20="ES",IF($Z20>150,GB 320000),IF($K20="IE",IF($Z20>150,GB 320000),IF($K20="IT",IF($Z20>150,GB 320000),IF($K20="DK",IF($Z20>150,GB 320000),IF($K20="NL",IF($Z20>150,GB 320000),IF($K20="CH",IF($Z20>0,CH)

  6. I am using the following formula, but I am finding examples where the SUM of T to V = 2 in the first argument and it is still returning a Compliant result when it should be Non Compliant for not being = to 3?

    =IF(OR(N46=1,SUM($T46:$V46)=3,N46=0,SUM($T46:$U46)=2),"Compliant","Non Compliant")

      • Simple formula, but I can't figure out how to use IF, or if it is IF OR or IF AND to nest the ifs.

        Column A (Salary) has values ranging from 10 to 100.
        I want to indicate in Column B whether the numbers in Column A would be, '75 and below,' '50 and below,' and '25 and below.'
        I can do the basic =IF(A2<=100,"100 or less","0") but then get stuck trying to add the '75 and below,' '50 and below,' and '25 and below.'
        Thanks!

  7. Hi,

    Thanks for you great works.

    I am working on a file with column A containing dropdown list of numbers 100, 200, and 300. The number represents "account department", "legal department" and "sales department" respectively.
    How can I make column B dependent on what is chosen on the dropdown list of column A? That is, if 100 is chosen on the dropdown list in column A, I want column B to return "account department" on its own.

    Thank yoy.

  8. I would like to calculate a sum of products, but with a pricing break.

    1st item= $50, 2nd item onwards = $70 each

    Let's say if A buys 3 products, he will have to pay $50 for the first product, for the other 2 items, he will have to pay $70 each.

    How could I create a formula for this problem? Hope you could assist me, it'll be a great help

    Thank you in advance

  9. I'm trying to use IF to show "ok" or "out of balance" if a value is over or under by more than 5%.
    Cell C20 has a value of 700
    Cell C21 has a value of 650
    My formula for D20 is =C20-C21 giving a value of 50
    My formula for D20 is =IF(D20< C20*5%,"Ok","Out of balance")
    This works but if the figures in C20 & C21 were reversed the value in D20 is -50 and shows as"Ok"
    What I want is the formula to show "out of balance" if the value was over OR under by more than 5%

  10. Cell I2="Any Text", J2="Blank Text,K2="Blank Text,L2="Blank Text,
    than need answer in Cell M="Any Text"

    Blank Text = Blank Cell

    one column have any text and other column have no text, I want to type text only automatically

  11. Is there a syntax error with this formula? I'm getting #Name. Likewise with this formula,

    =IF(AND(OR(AH2=”glass”,AH2=”stained glass”,AH2=”window”),I2>14,AA2>20),"Y","N")

  12. Hi there-
    I'm trying to code blood pressure according to JNC 7 criteria for normal/prehypertension/stage 1/stage 2 categories. i have different collumns for "systolic" and "diastolic" blood pressure numbers. A blood pressure can qualify for prehypertension, for example, if the systolic OR the diastolic numbers qualify. Here is what I have - can you help me figure out why it's not working?
    =IF(OR(G10 > 159,H10 > 99),"2",IF(OR(G10 > 139,H10 > 89),"1",IF(OR(G10 > 119,H10 > 79)"PRE",IF(G10 < 120,"NORM"))))

  13. Trying to combine these two IF statements into one IF OR statement:

    =IF(F:F<TODAY(),"Past Due", "Future Ship")

    =IF(G:G=H:H, "Picked" , "N/A")

    Any help would be appreciated!

    • Hi1
      What you want to do is not possible. These formulas use different values and are not connected in any way.Please re-check the article above.

  14. What is the best way to combine the two following statements. Combing is where I seem to have problems.

    =IF(AND(K2="Urgent"),IF(N21,"Fail")))

    =IF(AND(K2="Not Urgent"),IF(N23, "Fail")))

    Thank you

  15. i have customers data in excel how create customer wise statement a period of year or month

  16. Hello there!

    I could not get this formula to work. Could you help me identify where could be the error? It always gives a #VALUE! result.

    =IF(OR(B:B={"Third Party & Terminal PIU Unit","Shaybah Projects Inspection Unit","Dist & Refined Product P/L PIU Unit","RT Refinery & Juaymah NGL Unit","RTR Clean Fuel Complex Unit","Riyadh Refinery Unit","Cross Country Pipeline PIU Unit","Master Gas System Proj Inspection Unit","Pipeline Upgrade & Crude Delivery Unit","WR Refining & NGL Projs Insp Unit","WR Pipelines & Terminal Unit","WR Bulk Plant & Dist Unit","Jazan Complex Projs Inspection Unit"}), "DPID", ""), IF(OR(B:B={"Maritime Yard Dev Project Inspection Sec","Ship Building Projects Inspection Unit","Off, Rigs Platform & Utls Proj Insp Unit","Maint & Support Vessels Proj Insp Unit","Special Kingdom Projects Inspection Unit","Community Projects Inspection Unit","Communication & Security Unit","Batch Plants & Civil Testing Unit"}), "MBIPID", ""), IF(OR(B:B={"Gas Compression Projs Inspection Sec","NA Gas Facilities","SA Gas Facilities","Fadhili Project Insp Unit","Hawiyah Increment Projs Inspn Unit","Haw/Una Gas Reservoir Storage PIU","North Gas Comp Plants Proj Insp Unit","Haradh&Hawiyah Comp P/L Proj Insp Unit","Satellite Gas Comp Plants Proj Insp Unit","South Gas Comp Plants Proj Insp Unit","Infrastructure & Support Proj Insp Unit","Jafurah Util, Sulfur & Intrcon Sys PIU","Jafurah Gas Processing Trains PIU","Jafurah Pipelines, IT & Site Dev PIU","Jafurah Infra & 3rd Party Coord PIU","Wasit-Jafurah NGL Fractionation PIU","Jafurah Pipelines, & Downstream Fac PIU","NGL Recovery & Fract' Unit","Utilities, Flare & Piperack Unit","Site Prep, ISF, SSF Unit","Inlet Storage & Compression Unit","Downstream Pipeline Unit","Gas Treat, Sulfur Rec' & Han' Fac Unit","Unconventional Resources Projs Insp Unit"}), "UGIPID", ""), IF(OR(B:B={"SA Oil MP Projs Inspection Unit","NA Oil MP Projs Inspection Unit","Gas MP Projs Inspection Unit","Marjan Offshore Gas Facilities Unit","Marjan GOSP-4 Unit","Marjan Offshore Oil Facilities Unit","Marjan Onshore Oil Facilities Unit","Zuluf Onshore Facilities Proj Insp Unit","Zuluf Offshore Facilities Proj Insp Unit","Infras, Pipeline & Comm Proj Insp Unit","North Ghawar Oil Facilities Unit","NA Oil Facilities","South Ghawar Oil Facilities Unit","Berri Increment Processing Fac Unit","Berri Onshore Facilities Unit","Fabyards ProJ Insp Unit","Installation Projects Insp Unit","Onshore Proj Insp Unit"}), "UOPID", "")

    Many thanks.

    • Hi!
      Your formula is so big that it is impossible to understand it. It's not clear what you want to do. However, keep in mind that such a formula always returns an array of values.

      =IF(A1:A10={"a","b","c"},TRUE)

      See the result of this formula.

  17. “Gopal informed other students if you score 20 marks in end term exam OR 60 marks in total in
    subject then you PASS otherwise FAIL.” write an excel command.

  18. I need help,
    I have this scenario where Agent 1 has a ceiling of 500, Agent 2 has 250 and Agent 3 has 150.
    If at anytime any of the agents pay goes above the ceiling, then 10% is calculated on the ceiling if the pay is below the ceiling then the 10% is calculated on that amount

    How do i use IF statement to achieve this in Excel

  19. Hello,
    Is there a way to combine two formulas below:
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)

    Tried this way, but it's not working:
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0); OR(=IF(B63=TRUE; (G63)-(F63*1,21*D63); 0))

    Thank You for Your time!

    • Sorry, mistake! I meant:

      =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)
      =IF(H63="Paid"; (G63)-(F63*1,21*D63); 0

      Tried this way, but it's not working:
      =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0); OR(=IF(H63="Paid"; (G63)-(F63*1,21*D63); 0))

      • Hi!
        I think you have not read the article very carefully. There is an answer to your question.

        =IF(OR(B63=TRUE;H63=”Paid”);(G63)-(F63*1,21*D63); 0)

        • I really appreciate Your answer! Thank You!

  20. How To Extract Unique Values or Duplicate Names and sort (A-Z) Based On Criteria In Excel? Using index or match.

    For Example:-

    Sl No# Location Name score
    1 Mumbai Rohit 93
    2 Mumbai Sachin 93
    3 Gujrat Suresh Raina 90
    4 Ranchi M.S Dhoni 85
    5 Ranchi Sorabh Tiwari 85

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