To use Excel’s IF with OR, put OR in IF’s logical-test position: Excel checks whether at least one condition is true, then returns one result if it is and another if it is not. With a region in A2, for example, the formula below labels North or West as Regional and every other value as Other.
Assuming A2 contains the region name, enter: =IF(OR(A2="North",A2="West"),"Regional","Other"). If A2 contains North, the result is Regional; if it contains South, the result is Other.
How IF and OR work together
IF makes a decision based on a test. Its three arguments are the condition to test, the result to return when that condition is true, and the result to return when it is false. OR supplies a condition that can be true when any one or more of its tests is true. Put together, the functions let one IF decision account for several possible reasons to return the same result.
For example, a shipping rule might give a special label to orders going to either of two regions. Without OR, you could write nested IF functions, but that quickly becomes harder to read as you add conditions. OR keeps the alternatives together in one logical test, while IF keeps the outcomes together in one place.
The important distinction is that OR does not choose the result to display. It returns TRUE or FALSE. IF then uses that value to choose its true-result or false-result argument. If one test inside OR is true, OR returns TRUE, even if the other tests are false. If all of them are false, OR returns FALSE.
Basic syntax and argument order
The general structure is IF with an OR test in its first argument. OR can take multiple logical tests, separated by commas. The remaining IF arguments are the values or calculations to return for the two possible outcomes. Text results need straight double quotation marks; cell references and numeric values do not.
Assuming B2 contains a score and C2 contains Yes or No to indicate whether a submission is late, use: =IF(OR(B2>=90,C2="Yes"),"Review","Standard"). If B2 is 87 and C2 is Yes, the result is Review because the second test is true. If B2 is 87 and C2 is No, the result is Standard because both tests are false.
In this example, the tests are separated by a comma, and the comparison operators are part of the tests. You can use operators such as equals, greater than, less than, and not equal to. The two outcomes can be text, numbers, references, or other formulas, as long as each outcome makes sense for the workbook.
Excel’s regional settings can affect the separator shown in a formula. The examples here use commas, as in the standard English-language syntax; some installations expect semicolons instead. If Excel reports a formula error as soon as you enter a formula with commas, check the list separator used by your installation rather than changing the logic of IF and OR.
Practical examples for common tasks
IF OR is useful whenever several different conditions should lead to the same action. The tests can compare different cells, or check different values in the same cell. Make sure each example’s cell references match your own worksheet before filling the formula down.
- Flag a high or low reading: Assuming a measurement is in B2 and values below 10 or above 100 need checking, enter
=IF(OR(B2<10,B2>100),"Check","Within range"). If B2 is 105, the result is Check; if it is 50, the result is Within range. - Mark a row for follow-up: Assuming the status is in B2 and the balance is in C2, enter
=IF(OR(B2="Overdue",C2>500),"Follow up","No action"). If B2 is Current and C2 is 600, the result is Follow up because the balance test is true. - Check one of several department names: Assuming A2 contains a department, enter
=IF(OR(A2="Sales",A2="Support",A2="Operations"),"Customer-facing","Other"). If A2 is Support, the result is Customer-facing.
These are examples of inclusive OR: a match on any one test is enough. If a condition must be met together with another condition, use AND for that part of the test instead. For instance, a rule might require a score to be high and a status to be approved, rather than accepting either one by itself.
Assuming B2 contains a score and C2 contains an approval status, enter =IF(AND(B2>=80,C2="Approved"),"Eligible","Not eligible"). If B2 is 85 and C2 is Approved, the result is Eligible; if either requirement is not met, the result is Not eligible. This contrast helps prevent a common logic error: choosing OR when the rule actually requires every condition to be true.
Checking several values and maintaining criteria
For a short list of alternatives, writing each test directly inside OR is easy to understand. If the list changes often or contains many accepted values, hard-coding every value into a formula can make maintenance awkward. A separate list on the worksheet can make the allowed values easier to review and edit.
Assuming A2 contains a region and F2:F4 contains the accepted regions North, West, and Central, enter: =IF(COUNTIF($F$2:$F$4,A2)>0,"Regional","Other"). If A2 is West, COUNTIF finds one match and the formula returns Regional. If A2 is South, it finds no matches and the result is Other. The dollar signs keep the list range fixed if you copy the formula down.
This COUNTIF approach is not identical in every respect to a direct OR test. It is a convenient way to check whether one cell’s value appears in a list, especially when the list is maintained as worksheet data. Direct OR is more flexible when the conditions use different cells or different comparisons, such as a date test alongside a text test.
When you copy a formula down, Excel adjusts relative references such as A2 to A3, while absolute references such as $F$2:$F$4 remain fixed. That is usually what you want when each row has a different value to check against one shared list. Check the references in the first few copied rows to make sure the formula is evaluating the intended cells.
Choosing between OR, AND, and other approaches
Choose the function based on the rule, not on which formula looks shorter. OR means “one or more of these tests can be true.” AND means “all of these tests must be true.” A rule can also combine both: for example, an order may qualify if it is in either of two regions and its total is above a minimum. In that case, OR handles the region alternatives and AND combines that group with the total requirement.
Assuming the region is in A2 and the order total is in B2, enter =IF(AND(OR(A2="North",A2="West"),B2>=100),"Qualifies","Does not qualify"). If A2 is North and B2 is 120, the result is Qualifies. If A2 is South with the same total, the result is Does not qualify because the region requirement fails.
For several different outcomes rather than just a true and false outcome, a sequence of IF decisions or another suitable function may be clearer. But when multiple conditions all produce the same result, a single IF with OR is usually easier to follow than a chain of nested IF functions. Keep the formula readable: if the rule is difficult to explain in a sentence, consider documenting the criteria in nearby cells.
Common errors and troubleshooting
A formula can be syntactically valid and still give an unexpected answer if its logic or data does not match the intended rule. When the result is wrong, inspect the tests individually, then confirm that the IF outcomes and cell references correspond to the rule you want.
- Using OR instead of AND: OR accepts a row when any test is true. If every requirement must be met, use AND for those requirements.
- Missing quotation marks around text: Text comparisons such as a status name need quotation marks. Without them, Excel may treat the word as a name rather than as text.
- Unexpected spaces or inconsistent text: A cell that appears to say North may contain an extra space or a slightly different spelling. Compare the source values, and consider using a consistent dropdown list for entries that must match.
- Checking the wrong row: After copying a formula, verify that relative references moved as intended and that any fixed lookup range stayed fixed.
- Numbers stored as text: A value that looks numeric may be stored as text, which can affect comparisons. Check the cell’s contents and number format if a greater-than or less-than test behaves unexpectedly.
One practical way to debug a complicated rule is to temporarily test each comparison in a spare cell or inspect the formula one part at a time. If the OR result is true when you expect false, identify which individual condition is evaluating as true. If the logic is right but the displayed outcome is not, check the two results supplied to IF and confirm that they are in the intended order.
Entering and copying the formula in Excel
In Excel for Microsoft 365 on Windows, select the result cell, type an equals sign and the formula, then press Enter. To apply the result to additional rows, select the cell and drag its fill handle down, or copy and paste it into the destination cells. Double-clicking the fill handle can fill down alongside a neighboring column of data, though the exact extent depends on the adjacent data.
Excel for Mac uses the same IF and OR functions and the same formula logic. The main practical difference may be the configured argument separator, which depends on regional settings rather than on the function itself. Excel for the web also supports these standard functions. If a workbook is shared with people using different regional settings, check that the formulas calculate correctly in their Excel environment.
After entering a formula, test a case where the first condition is true, a case where a later condition is true, and a case where none are true. Those checks verify both the alternatives and the fallback result. For a workbook used by others, a short note explaining what each condition means can make the formula easier to maintain later.
Frequently Asked Questions
Can IF and OR check more than two conditions?
Yes. OR can test multiple logical conditions, and IF can use the result of that OR test to choose between two outcomes. Add each additional test as another OR argument, keeping the parentheses and separators balanced.
Does OR mean that all conditions must be true?
No. OR returns TRUE when at least one condition is true. Use AND when every condition must be true. Combining the functions is also possible when a rule contains both alternatives and requirements that must all be met.
Why does my IF OR formula return the wrong result?
Check whether the rule calls for OR or AND, then verify the compared values, text spelling, spaces, cell references, and the order of IF’s two outcomes. Testing each condition separately can reveal which part is producing an unexpected TRUE or FALSE result.
Can I use IF with OR for text values?
Yes. Put text values in straight double quotation marks in the comparison, as in a test for a particular status or region. Excel’s standard text comparisons are generally case-insensitive, but extra spaces or different spellings can still prevent a match.
What is the difference between IF OR and nested IF?
IF with OR is suited to several conditions that share the same outcome. Nested IF functions are useful when different conditions need different outcomes, but they can become difficult to read as they grow. Choose the structure that most clearly expresses the actual rule.
Related
- Small Excel Sample Data: Examples to Copy and Use Now
- Large Excel Data: Limits, Tools, and Best Practices
- Large Excel Cheat Sheet Mouse Pad: What to Know
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Split Cell Data in Excel: Text to Columns & FormulasTo split cell data in Excel, select the cells, choose Data > Text to Columns, pick Delimited or…
- INDEX Excel Arguments Explained: Syntax and ExamplesIn Excel, INDEX returns a value or reference at the intersection of a specified row and column in…
- Excel Formula for the Lower of Two Values in Two CellsTo return the lower of two values in Excel, use =MIN(A2,B2). If the values are in cells A2…
- Link Different Sheets in Excel with FormulasTo link a cell on one Excel sheet to a cell on another, enter a reference such as…
- Excel Timesheet Formula: Total Hours, Breaks, and ShiftsFor a timesheet with a start time in B2, an end time in C2, and an unpaid break…
- EDATE in Excel: Add or Subtract Months from a DateIn Excel, EDATE returns a date that is a specified number of months before or after a starting…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.