Excel IF, IFERROR, IFNA, and IFS — When and How to Use Each

Logical functions are essential in Excel for data cleaning, reporting, and analytics. These four functions help you apply logic and handle errors cleanly:

• IF → basic logic
• IFS → multiple conditions
• IFERROR → catch any error
• IFNA → catch only #N/A

IF — Basic Logical Test

Use IF when you want Excel to return different results based on a condition.

=IF(A2>50,"Pass","Fail")

Structure

IF(logical_test, value_if_true, value_if_false)

Common uses: thresholds, flags, labels, categories.

IFS — Multiple Conditions (Cleaner than Nested IFs)

Use IFS when you have many conditions to check.

Instead of nested IFs:

=IF(A2>90,"A",IF(A2>80,"B",IF(A2>70,"C","D")))

Use:

=IFS(
 A2>90,"A",
 A2>80,"B",
 A2>70,"C",
 TRUE,"D"
)

Cleaner and easier to read.

IFERROR — Handle Any Excel Error

Use IFERROR to replace any error (#DIV/0!, #N/A, #VALUE!, etc.).

=IFERROR(A2/B2,"Error")

Structure

IFERROR(value, value_if_error)

Common in calculations, lookups, and formulas that might break.

IFNA — Handle Only #N/A Errors

Use IFNA when working with lookups and you only want to catch missing matches.

=IFNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found")

This keeps other errors visible while handling only #N/A.

Structure

IFNA(value, value_if_na)

When to Use Each

Common Analytics Examples

Categorize values

=IF(A2>=100,"High","Low")

Grade ranges

=IFS(A2>=90,"A", A2>=80,"B", TRUE,"C")

Safe division

=IFERROR(A2/B2,0)

Lookup with fallback

=IFNA(XLOOKUP(A2,A:A,B:B),"Missing")

Key Takeaways

These are core functions for data cleaning and reporting in Excel

• IF handles simple logic
• IFS replaces nested IF statements
• IFERROR hides all errors
• IFNA hides only #N/A from lookups