Logical Functions — AND, OR, NOT, SWITCH | Excel Tutorial | Meritshot

What Are Logical Functions?

Logical functions test conditions and return TRUE or FALSE (or act on those results). They're the decision-making layer of Excel — when you need to categorise, flag, or route data based on rules, logical functions are the tool.

Boolean Values in Excel

TRUE  → displays as TRUE; in arithmetic = 1
FALSE → displays as FALSE; in arithmetic = 0

=TRUE + TRUE    → 2
=TRUE * 5       → 5
=FALSE + 10     → 10

This is why SUMPRODUCT((range="value") * amount_range) works — the condition creates 1s and 0s used as multipliers.

AND — All Conditions Must Be True

=AND(condition1, condition2, ...)

=AND(C2>70000, B2="Finance")
→ TRUE only if salary > 70000 AND department = Finance

=AND(A2>=60, A2<=100)
→ TRUE if A2 is between 60 and 100 inclusive

Return value: TRUE if ALL conditions are true; FALSE otherwise.

AND with IF

=IF(AND(C2>70000, B2="Finance"), "Eligible", "Not Eligible")
→ "Eligible" only if both conditions are met

OR — At Least One Condition Must Be True

=OR(condition1, condition2, ...)

=OR(B2="Finance", B2="Accounting")
→ TRUE if department is Finance OR Accounting

=OR(C2>90000, D2>5)
→ TRUE if salary > 90000 OR experience > 5 years

Return value: TRUE if ANY condition is true; FALSE only if ALL are false.

OR with IF

=IF(OR(B2="Finance", B2="Accounting"), "Finance Team", "Other")

NOT — Reverses a Logical Value

=NOT(condition)

=NOT(B2="Finance")     → TRUE if department is NOT Finance
=NOT(A2>0)             → TRUE if A2 is NOT positive (i.e., ≤ 0)
=NOT(ISBLANK(A2))      → TRUE if cell is NOT blank (i.e., has content)

NOT in Practice

=IF(NOT(B2="Finance"), "Other Team", "Finance Team")
-- Same as:
=IF(B2<>"Finance", "Other Team", "Finance Team")

Useful when combining with AND/OR:

=IF(AND(C2>70000, NOT(B2="Intern")), "Bonus Eligible", "Not Eligible")

Combining AND, OR, NOT

-- Multiple conditions with mix of AND and OR:
=IF(AND(OR(B2="Finance", B2="Tech"), C2>75000), "High Priority", "Standard")
→ High Priority if (Finance OR Tech) AND salary > 75000

-- Complex eligibility:
=IF(AND(D2>=3, NOT(F2="On Leave"), OR(C2>80000, E2="Manager")), "Eligible", "Not Eligible")

IFS — Multiple Conditions Without Nesting

IFS (Excel 2019+) replaces deeply nested IF chains:

=IFS(condition1, value1, condition2, value2, ..., TRUE, else_value)

=IFS(C2>=90000, "Band A",
     C2>=75000, "Band B",
     C2>=60000, "Band C",
     TRUE, "Band D")
→ TRUE at the end acts as the final "else" case

Equivalent nested IF (harder to read):

=IF(C2>=90000, "Band A", IF(C2>=75000, "Band B", IF(C2>=60000, "Band C", "Band D")))

SWITCH — Value-Based Branching

SWITCH (Excel 2019+) compares one value against multiple cases:

=SWITCH(expression, value1, result1, value2, result2, ..., [default])

=SWITCH(B2,
  "Finance",    "CFO Office",
  "Technology", "CTO Office",
  "Marketing",  "CMO Office",
  "Other")      → default if no match

Compare to nested IF:

=IF(B2="Finance", "CFO Office", IF(B2="Technology", "CTO Office", IF(B2="Marketing", "CMO Office", "Other")))

SWITCH is much cleaner when comparing one value against many specific cases.

IFERROR and IFNA

Handle errors gracefully:

=IFERROR(formula, value_if_error)
→ catches ANY error (#DIV/0!, #N/A, #VALUE!, #REF!, etc.)

=IFERROR(C2/D2, 0)                           → 0 if division by zero
=IFERROR(VLOOKUP(A2, Table, 2, 0), "Not Found")  → "Not Found" if #N/A

=IFNA(formula, value_if_na)
→ catches only #N/A (more specific than IFERROR)
=IFNA(VLOOKUP(A2, Table, 2, 0), "Missing")

Prefer IFNA over IFERROR for lookups — IFERROR would hide real errors like #REF!.

Logical Tests in Other Functions

Logical expressions return 1 (TRUE) or 0 (FALSE), which can be used directly in arithmetic:

-- Count employees in Finance over 80k (without COUNTIFS):
=SUMPRODUCT((B2:B10="Finance") * (C2:C10>80000))

-- Sum bonuses only for eligible employees:
=SUMPRODUCT((D2:D10="Eligible") * C2:C10 * 0.10)

IS Functions

Test what a cell contains:

Function	Returns TRUE when
`ISBLANK(A2)`	Cell is empty
`ISNUMBER(A2)`	Cell contains a number
`ISTEXT(A2)`	Cell contains text
`ISERROR(A2)`	Cell contains any error
`ISNA(A2)`	Cell contains #N/A
`ISLOGICAL(A2)`	Cell contains TRUE or FALSE
`ISDATE(A2)`	Cell contains a date (365 only)

=IF(ISBLANK(A2), "Missing", A2)           → show "Missing" for empty cells
=IF(ISNUMBER(A2), A2*1.10, A2)            → apply raise only if numeric
=IF(ISERROR(VLOOKUP(...)), "N/F", ...)    → older alternative to IFERROR

Practical Examples

Example 1: Employee Bonus Eligibility

Rules:

Performance rating ≥ 4 (column D)
Department is Finance or Technology (column B)
Not on probation (column E ≠ "Probation")

=IF(AND(D2>=4, OR(B2="Finance", B2="Technology"), NOT(E2="Probation")),
   C2 * 0.15,
   0)

Example 2: Lead Scoring

Score a sales lead based on multiple criteria:

-- Company size > 500 employees: +2 points
-- Industry = "Banking" or "Tech": +3 points
-- Annual revenue > 10 crore: +5 points

=((A2>500)*2) + (OR(B2="Banking",B2="Tech")*3) + ((C2>10000000)*5)
→ 0-10 score based on conditions

Example 3: Data Validation Check

Flag data quality issues:

=IFS(
  ISBLANK(A2),     "Missing Name",
  ISBLANK(B2),     "Missing Date",
  NOT(ISNUMBER(C2)), "Invalid Salary",
  C2<=0,           "Salary Must Be Positive",
  TRUE,            "OK"
)

Example 4: SLA Status

Customer support SLA: response within 4 hours, resolution within 24 hours:

A = Ticket Created, B = First Response, C = Resolution

=IF(ISBLANK(B2), "Awaiting Response",
  IF((B2-A2)*24 > 4, "SLA Breached (Response)",
    IF(ISBLANK(C2), "In Progress",
      IF((C2-A2)*24 > 24, "SLA Breached (Resolution)", "SLA Met"))))

Example 5: Dynamic Category with SWITCH

Classify transactions by amount:

-- This won't work (SWITCH matches exact values, not ranges)
Wrong: =SWITCH(C2, ">10000", "High", ...)

-- Correct approach: use IFS for range checks, SWITCH for exact values:
=IFS(C2>50000, "Very High",
     C2>10000, "High",
     C2>1000,  "Medium",
     TRUE,     "Low")

-- SWITCH is better for exact category codes:
=SWITCH(B2,
  "FIN", "Finance Department",
  "MKT", "Marketing Department",
  "TEC", "Technology Department",
  "Unknown Department")

Decision Tree for Logical Functions

One value vs. exact list of cases?
→ SWITCH

Multiple conditions, range-based?
→ IFS (or nested IF for older Excel)

All conditions must be true?
→ AND

At least one must be true?
→ OR

Reverse a condition?
→ NOT

Handle errors?
→ IFERROR (any error) or IFNA (#N/A only)

Test cell content type?
→ IS functions (ISBLANK, ISNUMBER, etc.)

Common Mistakes

1. Using AND/OR directly without IF

=AND(C2>70000, B2="Finance")   → returns TRUE/FALSE, not a useful label
=IF(AND(C2>70000, B2="Finance"), "Yes", "No")  → correct usage

2. Text comparison is case-insensitive

=B2="finance"   → matches "Finance", "FINANCE", "finance" equally
For exact: =EXACT(B2, "Finance")

3. Missing the "else" in IFS

=IFS(C2>90000, "A", C2>75000, "B")
→ If no condition matches → #N/A error
Fix: Always end with TRUE, "Default"

4. Nesting too many IFs (readability)

=IF(A, 1, IF(B, 2, IF(C, 3, IF(D, 4, 5))))   → hard to audit
Better: =IFS(A, 1, B, 2, C, 3, D, 4, TRUE, 5)

Practice Exercises

Write a formula that returns "Senior Manager" if department is Finance AND salary > 85000, "Manager" if Finance AND salary > 70000, "Staff" otherwise.
Flag employees as "At Risk" if they have been with the company for < 1 year OR their performance rating is < 3.
Build a lead scoring formula: +5 if company size > 1000, +3 if industry is "Banking", +2 if they opened the email (column says "Yes").
Use SWITCH to convert department codes ("FIN", "MKT", "OPS", "TEC") to full department names.
Write a data quality check formula that flags rows as "OK" or identifies the first problem found (missing name, invalid date, zero salary).

Summary

In this chapter you learned:

AND(c1, c2, ...) — TRUE if ALL conditions are true
OR(c1, c2, ...) — TRUE if ANY condition is true
NOT(condition) — reverses TRUE/FALSE
IFS(c1, v1, c2, v2, ..., TRUE, default) — multi-branch logic without nesting
SWITCH(expr, val1, result1, ..., default) — match one value against exact cases
IFERROR(formula, fallback) — catch any error; IFNA catches only #N/A
IS functions: ISBLANK, ISNUMBER, ISTEXT, ISERROR — test cell type
Logical results (TRUE/FALSE) = (1/0) in arithmetic — use in SUMPRODUCT
Prefer IFS and SWITCH over deeply nested IF for readability
Always include a default/else in IFS to prevent #N/A

Next up: Statistical Functions — SUMIF, AVERAGEIF, COUNTIF families, and data analysis functions.

Logical Functions — AND, OR, NOT, SWITCH