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 "cancelled", 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", "")

IF OR formula to return nothing if none of the conditions is met.

Should the last argument be omitted, the formula would 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 2016 - 2007 formulas, 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!

You may also be interested in:

32 Responses to "Excel IF OR function with formula examples"

  1. Craig Gordon says:

    Hi there

    please assist
    if A>=2(Fail),if b>=3(Fail),but if A:B>=3(Fail)

    How do i get this into one fomula

    • Marty says:

      So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
      Thanks in advance!

  2. Shobi Imran says:

    Very good article, thanks for sharing, Keep up the good work!

  3. girineo1 says:

    Hi. Can you help me. i have the following. Column Q is a sum of hours for operations. Column Y is my set hour reset. Y2 Starts at 120 hours and ends at Y23 at 2640 hours. increments are in 120 hours.
    In Columm Q i have the following formula =Sum(K3+Q2). on R3 i want to add a formula to do the following.

    If cell Q3 is 120240 then subtract Q3-$Y$2, or if Q3 is 241360 then Subtract Q3-$Y$3, or if Q3 is 361480 then subtract Q3-$Y$4, if false then add K3+Q2

    Whats the best way for me to write it.

    i manage to write a formula but it turns the cell in Column R when ever the statement is true "true".

  4. Victor says:


    Thanks for your teach, but i think that is better to use brackets, especially if B2 can have several values

    =IF(OR(B2={"delivered";"paid"});"Closed"; "")


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


  5. Rick says:

    I need the response in column D , labeled "link", to substitute the number of the column with the actual entry in that column of the row. The desired results, column E, are in the "want" column. Any thoughts?

    1 2 3 LINK WANT
    J18.9 A41.9 1 J18.9
    R41.82 E86.0 E43 2 E86.0
    G20 R26.89 G30.9 1,3 G20, G30.9

  6. babarbuttgrt says:

    Hi everyone

    I need little help to construct formula from below pseudo code.

    customer = private AND account_status = active AND account_open_date >23-June-2006
    If {
    risk = high
    Last Review date = 1st review date + 6 Months
    Else If{
    risk = medium
    Last Review date = 1st review date + 12 Months
    Else If{
    risk = low
    Last Review date = 1st review date + 12 Months
    Else {
    last review date = 24-June-2006
    If {
    customer = govt AND account_open_date 23-June-2006
    If {
    risk = high
    Last Review date = 1st review date + 6 Months
    Else If {
    risk = medium
    Last Review date = 1st review date + 12 Months
    Else If{
    risk = low
    Last Review date = 1st review date + 12 Months

  7. Brett says:

    If I want to reference three cells, what's the formula? (i.e =IF(F113-"x",(J126)),=IF(G113-"x",(K126)),=IF(H113-"x",(K126))

    H126 want to be the value of one of three cells depending the selection of another value in three cells

    Thanks in advance

  8. Peter says:

    If F34 value = "Dealer", then used values Column K OR
    If F34 value = "Trade", then use values Column M OR
    If F34 value = "End User", then use values Column

  9. Laura says:

    I am trying to evaluate if the first date is a weekend or the time is after 5pm.
    Each works on its own but is not working when combines with the OR
    I am getting #NAME?
    =IF(OR(WEEKEND(E2,2)>5,K19>TIME(17,0,0)),"OT", "REG")

    Any Ideas

    • Laura says:

      Apparently I just needed to retype and press the keyboard harder:) it worked the 50th time I typed it I don't know why as it looks exactly the same. But for now I will move on.

  10. Mark Whiteley says:

    Hi, Hoping someone can help.
    I'm trying to write a formula using the IF, AND, or OR function but can't get the formula correct. It should be simple, really.
    The conditions are;
    If the SUM of Cells E4:G4 = between 10 and 15, then Cell G14 = 25
    If the SUM of Cells E4:G4 is greater than or equal to 15, then Cell G14 = 50

    Then there's one other result that I'm trying to achieve (in a separate cell but a similar formula)
    If the SUM of Cells E4:P4 = between 400 and 600, then Cell P15 = 10
    If the SUM of Cells E4:P4 is greater than or equal to 600, then Cell P15 = 20
    I can't work out what I'm doing wrong, I wont paste what formulas i currently have to avoid causing a case of mass confusion.

  11. Vikash says:

    Hi all
    I want to differentiate the cell values into the crores, Lakh, Thousand, Hundred, Tens, Units
    Example : -
    12 Crores 24 Lakh 56 Thousand 7 Hundred 89
    So how will i do can anyone here who can help me

  12. Marty says:

    So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
    Thanks in advance!

  13. farzaneh says:

    hi everyone,
    how can i formulate this one?
    if >=6 : full assistance
    if =4 or 5 : half assistance
    if <= 3 : no assistance

  14. MuiMui says:

    I need to validate customer order to ensure it is not less than minimum order value(MOV) and not less than minimum order qty. We validated order value on one column, filter out the order lines with order value lower than MOV and then validated order qty on another column. Is there shortcut to have all the validation performed under single column with a sophisticated nested if function?

  15. jwalker says:

    I am looking for the correct formula to use to return the greatest of two values. For example, if Q3 (5.89) is greater than R3 (7.452), I want S3 to show R3 value (7.452). If Q17 (28.86) is greater than R17 (3.105), I want S17 to show Q17 value (28.86).

    • Hi Jwalker,
      I hope that your task may be expressed in the following way: if the value in Cell R3 is less than the value in Cell Q3, the value from Cell Q3 is needed; if the value in Cell R3 is more than the value in Cell Q3, the value from Cell R3 is needed. If so, here is the formula you could apply:

      =IF(Q3 < R3, R3, Q3)

  16. aimo says:

    Formula 2. If a cell is this OR that, then calculate
    =IF(OR(D3>0,D390,D3180,D3270,D30,D390,D3180,D3270,D3<360),W3] are not coming
    - need to know what to change to make it work .. than you

  17. Björne says:

    Someone please help me, i cant get this to work

    In column C I enter one of 7 names.
    Depending on the name I want different results in column N
    If the name is
    1 - Andy Black the result should be 400
    2 - Mr Jet, Nina Sven or Mike Young the result should be 600
    3 - Dr Joe, Miss Adams or Neil Foe the result should be 800
    4 - Ms Hard the result should be 1000
    5 - Mr Woo the result should be 1200

    Which formula do I use to solve this?

  18. Sue M says:

    I have 2 columns, work email(D2) & personal email(E2). I am trying to create a formula in a new field (preferred email) that says if D2 is blank use E2 (if there is a value) or if E2 is blank use D2 or leave blank. Is this possible?

    • Hi,
      If I get it right, your task is as follows: if a cell in Column D contains an email address, a formula is to bring it; if not, it should bring an email address from a cell in Column E; if both cells are empty, the formula has to bring nothing. I hope you do not mind lengthy formulas:

      =IFS(OR(AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=0), AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=1)), D2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=0), E2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=1), "")

    • Hi Sue,

      If you love compact formulas, use this one :)

      =IF(D2<>"", D2, IF(E2<>"", E2, ""))

  19. Tina Owens says:

    I need a formula in google spreadsheet that will:
    +1 when the value is >=5,
    +2 when the value is >=10,
    +3 when the value is >=15,
    +4 when the value is >=20,
    +5 when the value is >=25
    The formula I am currently using is:
    This formula is working for the +1 when the value is >=5, but when the value is >=10, it is still adding +1.
    Please Help!

    • Hello Tina,

      You see, your first condition fits to all other conditions as well - the value is greater than 5. You need to limit each condition and check, for example, if the number is not only greater than or equal to 5 but also less than 10.
      Your formula for spreadsheets should look like this:
      =IF(AND(F7>=5,F7<10), H7+1, IF(AND(F7>=10,F7<15), H7+2, IF(AND(F7>=15,F7<20), H7+3, IF(F7>=20,H7+4, H7))))

      You will find the info about the IF function in Google Sheets in this post.

  20. carter harden says:

    I have student totals,I want to apply comments, 400 and above should have good performance, 300-400 should have fair performance, below 300 should have poor performance,the cell for total is I

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard