How to If This And That Or That in Excel

Why This Task Matters in Excel

Logical decision-making is one of the most common activities in any spreadsheet. Whether you manage budgets, track projects, or analyse customer behaviour, you frequently need Excel to produce one result when several conditions are met and a different result when they are not. The pattern often sounds like plain English: “If this and that, or that, then do X, otherwise do Y.” Translating that natural-language thought into a single, reliable Excel formula is the essence of the “If This And That Or That” task.

Consider a sales manager who wants to mark an order for free shipping if the order value is at least 500 USD and the customer is in the loyalty program, or the order value is at least 1 000 USD regardless of loyalty. Human conversation makes that decision easy; the spreadsheet must interpret the same logic exactly the same way hundreds of times per day. In human resources, you might grant holiday carry-over only if an employee has at least five unused days and a tenure of more than three years, or the request has been pre-approved by a director. Compliance teams, credit analysts, invoice processors, and quality assurance engineers all run into comparable multi-condition rules.

Excel is extremely well suited to these boolean evaluations because it offers dedicated logical functions (AND, OR, NOT), comparison operators such as =, ‑, and <=, and wrappers like IF, IFS, and SWITCH that convert true / false outputs into playable business actions. Once you master a clear way of chaining AND and OR inside IF, you can build dashboards that categorise records automatically, trigger conditional formatting, feed pivot-table filters, or generate alerts without manual review. Failing to understand operator precedence or the correct grouping of conditions can lead to costly mis-classifications: a mis-routed shipment, an incorrectly denied rebate, or an inaccurate compliance flag can all trace back to a single misplaced parenthesis. By getting comfortable with “If This And That Or That” logic, you strengthen your overall data modelling skills and pave the way for more advanced topics such as array-enabled filtering, nested LET statements, and full automation with Power Query or VBA.

Best Excel Approach

The most universal and transparent way to build a “this AND that OR that” rule is to combine AND and OR inside a single IF formula, using parentheses to make the intended precedence crystal clear. AND and OR each return either TRUE or FALSE. IF then converts that logical result into whatever numeric, text, or further formula result you wish.

Recommended syntax:

=IF(OR(AND(condition1,condition2),condition3),value_if_true,value_if_false)

Why this layout? Excel always evaluates AND before OR when they share the same nesting level. By explicitly wrapping condition1 AND condition2 inside an AND function and then surrounding that AND with an OR that also contains condition3, we guarantee the statement “condition1 and condition2, or condition3.” Using this canonical pattern keeps formulas readable for collaborators and reduces debugging time. It also works identically in all Excel versions released in the last 20 years, including Microsoft 365, Excel 2019, and even Excel 2007.

Alternative when more than two possible outcomes exist:

=IFS(AND(condition1,condition2),result1,condition3,result1,TRUE,result2)

IFS can reduce nesting in certain multi-branch scenarios but is available only in Excel 2019 onward and in Microsoft 365. For basic two-outcome tests, classic IF with AND/OR is still the fastest to write and the easiest to audit.

Parameters and Inputs

1. Conditions

• Each condition must evaluate to a TRUE or FALSE value. Typical forms include comparisons like A2 greater than 100, equality checks such as B2="Yes", or results from ISBLANK, ISTEXT, and similar tests.

2. value_if_true / value_if_false
   • These outputs can be anything: text, numbers, cell references, formulas, or even dynamic array results such as FILTER.
3. Data Types

• Numeric and date comparisons require underlying values stored as numbers. If numbers arrive formatted as text, convert them first with VALUE or Text to Columns.

4. Optional Wrappers

• Functions like TRIM, UPPER, and VALUE may be required to normalise data before comparison.

5. Edge-Case Handling

• Watch for blank cells that might produce a FALSE numeric comparison while still looking like “nothing wrong.” Use ISNUMBER or LEN if blanks carry meaning.

6. Validation

• Where possible, keep allowed values in drop-down lists to guarantee consistent spelling and capitalisation.

Step-by-Step Examples

Example 1: Basic Scenario – Free Shipping Eligibility

Assume an online store offers free shipping when an order value is at least 500 USD and the customer belongs to the loyalty club, or when the order value is at least 1 000 USD regardless of loyalty. Your worksheet contains:

Fill [A2:A6] with 350, 520, 980, 1100, 440.
Fill [B2:B6] with \"Yes\", \"No\", \"Yes\", \"No\", \"Yes\".

1. Click C2.
2. Enter:

=IF(OR(AND(A2>=500,B2="Yes"),A2>=1000),"Yes","No")

3. Copy the formula to [C2:C6].

Expected results: rows two, four, and five yield “No,” while three and four show “Yes.” Notice that row three qualifies on 980 USD plus loyalty; row four qualifies on 1 100 USD holiday spend even without loyalty, and row five does not qualify because it meets neither threshold nor loyalty.

Why this works:

• The AND part requires both a numeric test and a text test.
• The OR test then compares the composite AND result to the stand-alone numeric threshold, satisfying our “and that, or that” requirement.

Troubleshooting:
If every result turns into “No,” check that currency values are numeric (no stray apostrophe) and that loyalty values match the exact case used in the formula. Using B2="yes" would fail because Excel treats “Yes” and “yes” as different strings.

Example 2: Real-World Application – HR Holiday Carry-Over

Human Resources needs to allow employees to carry unused holiday days into the next year only if they have at least five unused days and at least three years of service, or if the request has been pre-approved by HR leadership. The dataset:

Steps:

1. Populate rows 2 – 6 with example employees:

• Michael: 6 days, 4 years, \"No\"
• Laura: 3 days, 6 years, \"Yes\"
• Aisha: 8 days, 2 years, \"No\"
• Eduardo: 12 days, 5 years, \"No\"
• Hana: 4 days, 1 year, \"Yes\"

2. In E1, label “Carry Over Allowed.”
3. In E2, type:

=IF(OR(AND(B2>=5,C2>=3),D2="Yes"),"Allowed","Denied")

4. Copy E2 downward.

Results:

• Michael: Allowed (meets both unused days and tenure).
• Laura: Allowed (director approval overrides).
• Aisha: Denied (tenure too short, no approval).
• Eduardo: Allowed (both conditions met).
• Hana: Allowed (director approval overrides).

Integration Tip: Feed the resulting TRUE/FALSE text into a pivot table to summarise by department, or into conditional formatting to highlight employees who need follow-up.

Performance Note: Even if you scale this to several thousand employees, IF with basic comparisons is virtually instantaneous because it uses simple scalar calculations rather than array reshaping.

Example 3: Advanced Technique – Dynamic Array Filter for Credit Risk

A risk analyst wants to list high-risk loans meeting complex criteria: loan amount above 250 000 USD and credit score below 600, or any loan with more than two missed payments. Using Microsoft 365 dynamic arrays, place data in an Excel table named Loans with fields [LoanAmount], [CreditScore], [MissedPayments].

1. Select an output cell, say H2.
2. Enter:

=FILTER(Loans, (Loans[LoanAmount]>250000)*(Loans[CreditScore]<600) + (Loans[MissedPayments]>2))

Explanation:
Excel treats TRUE as 1 and FALSE as 0. Multiplication acts like AND, addition acts like OR inside the logical array. This purely mathematical boolean build avoids nested AND/OR functions and can be faster on very large data sets. The FILTER function spills the entire matching record set, saving time otherwise spent copying formulas row by row.

Error Handling: If no loan meets the criteria, FILTER returns #CALC!. Add an IFERROR wrapper to display a friendly message.

=IFERROR(FILTER(Loans,(Loans[LoanAmount]>250000)*(Loans[CreditScore]<600)+(Loans[MissedPayments]>2)),"No high-risk loans")

Best Practice: Give each numeric test its own LET variable to enhance readability and performance.

=LET(
    amt, Loans[LoanAmount]>250000,
    score, Loans[CreditScore]<600,
    missed, Loans[MissedPayments]>2,
    FILTER(Loans, (amt*score)+missed)
)

The LET approach calculates each logical array once, which matters when hundreds of thousands of rows are present.

Tips and Best Practices

1. Always use parentheses liberally. Never rely on your memory of operator precedence in stressful audits.
2. Convert text to consistent case using UPPER or LOWER before equality checks to avoid mismatches.
3. Store “control values” such as thresholds or approval flags in separate named range cells so that changes require zero formula edits.
4. Test complex logic step by step: first return the AND result alone, then wrap it inside OR, then embed IF. This isolates errors quickly.
5. For dynamic array tasks, consider boolean math (* for AND, + for OR) plus LET variables to keep formulas compact and efficient.
6. Document your logic with cell comments or the new Note feature so coworkers know exactly why a record met the rule.

Common Mistakes to Avoid

1. Missing parentheses

• Excel will still calculate but interpret “IF this OR that AND that” incorrectly because AND takes priority.
• Fix: Wrap the AND portion inside parentheses or an explicit AND function.

2. Mixing text and numbers

• Comparing a numeric threshold to a cell that contains “300 USD” stored as text leads to FALSE.
• Fix: Strip out symbols with SUBSTITUTE or convert text numbers with VALUE.

3. Case-sensitive mismatches

• B2="yes" returns FALSE when the cell shows “Yes.”
• Fix: Use UPPER on both comparison elements or rely on case-insensitive functions like SEARCH coupled with ISNUMBER.

4. Hard-coding thresholds in multiple places

• Inconsistent updates create hidden logic drift across the workbook.
• Fix: Centralise thresholds in one parameter cell and reference it throughout.

5. Ignoring blank cells

• A blank cell compared to a number yields FALSE but can still be significant in audit trails.
• Fix: Include ISBLANK or LEN tests when blanks carry meaning.

Alternative Methods

Switch between methods based on audience skill level, version constraints, and performance requirements. For worksheets shared with external partners, pick the oldest common denominator—usually classic IF.

FAQ

When should I use this approach?

Use this pattern whenever you need different outcomes based on a compound rule containing both AND and OR components. Examples include tiered pricing, staff eligibility checks, and compliance flags.

Can this work across multiple sheets?

Yes. Reference conditions from other worksheets by prefixing the sheet name, such as AND(Sheet2!B2="Yes",Sheet3!A2 ≥ 500). Keep workbook structure stable to avoid broken links.

What are the limitations?

Classic IF supports only two outcomes. If you need more branches, nest additional IFs or switch to IFS, SWITCH, or a lookup table. Also, very long strings in formulas become hard to audit.

How do I handle errors?

Wrap the entire formula in IFERROR to substitute a custom message. Within conditions, pre-check for division by zero or improper data types using ISNUMBER, ISTEXT, and similar functions.

Does this work in older Excel versions?

IF with AND and OR runs fine back to Excel 97. IFS, LET, and dynamic FILTER require Excel 2019 or Microsoft 365. Create a compatibility matrix if you distribute workbooks widely.

What about performance with large datasets?

Simple scalar formulas calc quickly even on 100 000 rows. For millions of rows, use dynamic array functions or push logic into Power Query or a database, then aggregate results back into Excel.

Conclusion

Mastering the “If This And That Or That” pattern empowers you to model real-world decision logic directly in Excel, removing guesswork and manual intervention. You now know how to craft robust formulas with IF, AND, OR, and modern alternatives like IFS and dynamic FILTER, plus how to troubleshoot and optimise them. Add this skill to your toolkit, experiment with your own scenarios, and soon you will handle even more involved business rules with confidence and precision.