IFNA and IFERROR Functions
Excel Guide: IFNA vs IFERROR Functions
1. IFERROR Function
🔍 What is IFERROR?
IFERROR is used to catch any kind of error in a formula (like #DIV/0!, #N/A,
#VALUE!, etc.) and return a custom value instead.
📌 Syntax:
IFERROR(value, value_if_error)
🧩 Parameters:
value – The expression/formula to check.
value_if_error – The result to return if there's an error in the expression.
🧾 Example:
Data:
A | B
10 | 2
8 | 0
5 | (blank)
Goal: Divide A by B and avoid #DIV/0! or other errors.
Formula: =IFERROR(A2/B2, "Error")
Results:
- 10 ÷ 2 = 5
- 8 ÷ 0 = Error
- 5 ÷ (blank) = Error
🪜 Step-by-Step:
1. Select a blank cell.
2. Type =IFERROR(.
3. Enter the formula to check (e.g., A2/B2).
4. Add the result to return if there's an error (e.g., "Error").
5. Press Enter.
2. IFNA Function
🔍 What is IFNA?
IFNA is more specific — it only handles the #N/A error (common in lookup
functions), and returns a custom message if that specific error occurs.
📌 Syntax:
IFNA(value, value_if_na)
🧩 Parameters:
value – The formula or expression to evaluate.
value_if_na – The result to return if the formula returns #N/A.
🧾 Example:
Data:
A
Apple
Mango
Pear
Goal: Find "Orange" using VLOOKUP.
Formula: =IFNA(VLOOKUP("Orange", A2:A4, 1, FALSE), "Not
Found")
Result: Not Found
🪜 Step-by-Step:
1. Enter a lookup formula that may return #N/A.
2. Wrap the formula with IFNA(.
3. Add a friendly message or alternative result.
4. Press Enter.
Key Differences Between IFNA and IFERROR
Feature |
IFNA | IFERROR
------------------|-------------------------------|------------------------------
Handles only | #N/A errors | All errors (#DIV/0!, etc.)
Use case | Best for lookup
functions | Best for math or general
use
Available since | Excel 2013+ | Excel 2007+
More specific? | Yes | No
📝 Tips
- Use IFNA when you're working with VLOOKUP, HLOOKUP, MATCH,
etc. and want to catch just #N/A errors.
- Use IFERROR when you want to catch any kind of error.