Tag: excel

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

FunctionUse When
IFYou have one condition
IFSYou have multiple conditions
IFERRORYou want to hide all errors
IFNAYou only want to handle missing lookup results

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