Tutorials›Excel›Logical Functions
🟢 Free Demo
Excel TutorialTopic 24 of 31

Logical Functions

Master AND, OR, NOT, IFERROR, IFNA, SWITCH and XOR to build clean conditional logic in Excel.

✅ What You Will Learn

›How AND() and OR() combine multiple conditions in one test
›How IFERROR and IFNA handle formula errors gracefully
›How SWITCH() evaluates a value against multiple options
›How NOT() reverses a logical test
›How to combine logical functions with IF for complex decision logic

Logical functions evaluate conditions and return TRUE or FALSE — or control what happens when a condition is met or when a formula produces an error. They are the building blocks of decision logic in Excel.

AND() returns TRUE only when all its arguments are TRUE. OR() returns TRUE when at least one argument is TRUE. These two functions are almost always used inside IF() to test compound conditions rather than on their own.

IFERROR() is one of the most practically useful functions in Excel — it catches any error a formula might produce and returns a specified value instead. Every VLOOKUP, division, or text extraction that might fail should be wrapped in IFERROR() in a professional report.

📋 Loan application evaluation — multiple conditions

ApplicantCredit ScoreIncome (₹)Existing LoansEligible?
Rahul Sharma7508000001AND(score>700, income>500k, loans<3)
Priya Verma6209500000AND(score>700, income>500k, loans<3)
Amit Kumar7804200002AND(score>700, income>500k, loans<3)
Sneha Kapoor81012000000AND(score>700, income>500k, loans<3)

Syntax

EXCEL SYNTAX
=AND(logical1, logical2, ...)   → TRUE only if all are TRUE
=OR(logical1, logical2, ...)    → TRUE if at least one is TRUE
=NOT(logical)                   → reverses TRUE/FALSE
=XOR(logical1, logical2)        → TRUE if exactly one is TRUE (exclusive OR)
=IFERROR(value, value_if_error) → returns value_if_error if value causes any error
=IFNA(value, value_if_na)       → returns value_if_na only for #N/A errors
=SWITCH(expression, val1, result1, val2, result2, ..., [default])

Examples

Example 1AND + OR — multi-condition loan eligibility
=IF(AND(B2>700, C2>500000, D2<3), "Eligible", "Not Eligible")
OUTPUT
Rahul: Eligible  |  Priya: Not Eligible (score 620)  |  Sneha: Eligible
💡

AND requires ALL conditions true. Change to OR if any one condition being true should qualify the applicant: =IF(OR(B2>750, C2>1000000), "Review", "Reject")

Example 2IFERROR — prevent #DIV/0! and #N/A from breaking reports
=IFERROR(C2/D2, 0)                  → 0 instead of #DIV/0! when D2=0
=IFERROR(VLOOKUP(A2, Table, 2, 0), "Not Found")  → friendly message
💡

IFERROR catches ALL error types. IFNA catches only #N/A. Use IFNA with lookups when you want division errors to still show as errors but missing lookups to show "Not Found".

Example 3SWITCH — cleaner alternative to nested IFs for fixed values
=SWITCH(B2, "North","N","South","S","East","E","West","W","Other")
=SWITCH(WEEKDAY(TODAY(),2), 1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday","Weekend")
💡

SWITCH tests one expression against a list of values. When the values are exact matches (not ranges), SWITCH is more readable than nested IFS. The last argument is the default.

📌 Key Points to Remember

  • ✓AND and OR take up to 255 logical arguments — chain conditions without nesting
  • ✓IFERROR catches #DIV/0!, #N/A, #REF!, #VALUE!, #NAME?, #NULL! and #NUM! — all Excel error types
  • ✓IFNA only catches #N/A — use it when you want other errors (like #VALUE!) to remain visible for debugging
  • ✓SWITCH is available in Excel 2019+ and Microsoft 365 — use IFS or nested IF in older versions
  • ✓NOT() reverses the result: =IF(NOT(ISBLANK(A1)), "Has data", "Empty")

🏢 Real-World Application

Credit risk teams use AND() with multiple scoring criteria to auto-approve or reject loan applications. Retail analysts use IFERROR around division formulas (profit margin = profit/revenue) so that months with zero revenue show 0% instead of breaking the dashboard. SWITCH is widely used in payroll to convert department codes to department names without a lookup table.

⚠️ Common Mistakes to Avoid

WRONGNesting multiple IF instead of using AND: =IF(A1>0,IF(B1>0,"Both","No"),"No")
FIXUse =IF(AND(A1>0,B1>0),"Both","No") — cleaner, easier to read, and less error-prone when conditions grow.
WRONGUsing IFERROR to hide genuine errors during formula development
FIXBuild and test formulas without IFERROR first. Add it only after the formula is confirmed correct. IFERROR hiding errors during development makes bugs impossible to find.
WRONGSWITCH not finding a match and showing #N/A when no default is provided
FIXAlways provide a default value as the last argument to SWITCH: =SWITCH(A1, "A",1, "B",2, "Unknown"). Without it, unmatched values return #N/A.
✏️Test Yourself

What does =AND(5>3, 10<8, 7=7) return?

❓ Frequently Asked Questions

What is the difference between IFERROR and IFNA?

IFERROR traps all Excel error types. IFNA traps only #N/A errors. Use IFNA with VLOOKUP/MATCH when you want to show "Not found" for missing lookups but still want to see #VALUE! or #REF! errors that indicate real formula problems.

Can AND and OR be used outside IF?

Yes — they return TRUE or FALSE directly. =AND(A1>0, B1>0) in a cell shows TRUE or FALSE. They are often used in Conditional Formatting formula rules and Data Validation formula rules.

What does XOR do?

XOR (exclusive OR) returns TRUE when exactly ONE of the conditions is true, not when both are true. =XOR(TRUE, TRUE) returns FALSE. It is rarely used in business analysis but appears in some logic-checking scenarios.

✏️ Practice Exercise

Build an automated loan eligibility checker. Input fields: Credit Score, Monthly Income, Existing EMI, Loan Requested. Using AND, OR, and IF, create rules: (1) Auto-Approve if score>750 AND income>3x EMI AND requested<50x income, (2) Manual Review if score 650-750 OR income borderline, (3) Reject otherwise. Add IFERROR around any division. Display result, reason, and maximum eligible loan amount.

← PreviousFinancial FunctionsNext →Math Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.