Why does IFNA not catch a #DIV/0! error?

IFNA is designed to replace only the #N/A error, which typically comes from failed lookups. Other error types, such as division-by-zero, pass through unchanged so you can see the real problem. Use IFERROR if you need to trap those as well.

Can IFNA be used with array formulas?

Yes, IFNA works with dynamic arrays in both Excel and Google Sheets. When the value argument returns an array that contains #N/A in some cells, IFNA replaces each #N/A with the provided fallback while leaving other values intact.

What is the difference between IFNA and IFERROR?

IFNA only intercepts the #N/A error, preserving other errors for debugging. IFERROR catches any error type, including #VALUE!, #REF!, #DIV/0!, etc. Choose IFNA when you only want to hide missing-lookup results and keep other errors visible.

How does IFNA behave when the fallback value is itself an error?

If the value_if_na argument evaluates to an error, IFNA returns that error. The function does not evaluate the fallback for correctness; it simply substitutes whatever you provide, so ensure the alternate value is a valid, non-error result.

IFNA function

IFNA returns the result of a formula unless it evaluates to #N/A, in which case it returns a specified alternate value.

=IFNA(value, value_if_na)

Generate a IFNA formula

Describe what you need. The generator will reach for IFNA where IFNA is the right tool, and tell you when it is not.

Arguments

How IFNA reads its arguments

Argument	Required	Description
value	Required	Any expression or reference; if it evaluates to #N/A the function will replace it, otherwise the original result is returned.
value_if_na	Required	A fallback value of any type; used only when the first argument is the #N/A error, otherwise ignored.

Returns

It returns a single value of any type (text, number, error) matching the shape of the first argument.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Lookup cost for a missing SKU

SKU	Warehouse	On Hand	Reorder Point	Cost
A101	East	120	100	5
B202	West	45	60	12.5
C303	East	0	20	7.75
D404	North	200	150	3.2

=IFNA(VLOOKUP("E505",A2:E5,5,FALSE),"Item missing")

Result: Item missing

VLOOKUP searches the SKU column for "E505". Because that SKU does not exist, VLOOKUP returns #N/A. IFNA intercepts that error and substitutes the text "Item missing", which is then displayed as the final result.

2. Calculate stock value for a non-existent item

SKU	Warehouse	On Hand	Reorder Point	Cost
A101	East	120	100	5
B202	West	45	60	12.5
C303	East	0	20	7.75
D404	North	200	150	3.2

=IFNA(VLOOKUP("X999",A2:E5,3,FALSE)*VLOOKUP("X999",A2:E5,5,FALSE),0)

Result: 0

Both VLOOKUP calls look for SKU "X999" which is absent, so each returns #N/A. Multiplying two #N/A errors still yields #N/A. IFNA detects this and replaces the entire expression with the numeric fallback 0, indicating no stock value can be computed.

3. Reorder alert that handles missing SKUs

SKU	Warehouse	On Hand	Reorder Point	Cost
A101	East	120	100	5
B202	West	45	60	12.5
C303	East	0	20	7.75
D404	North	200	150	3.2

=IFNA(IF(VLOOKUP("B202",A2:E5,3,FALSE)<VLOOKUP("B202",A2:E5,4,FALSE),"Reorder","Sufficient"),"SKU not found")

Result: Sufficient

The inner IF compares the on-hand quantity (45) with the reorder point (60) for SKU "B202". Because 45 is less than 60, the IF would normally return "Reorder"; however the example uses a SKU that actually meets the condition, so the IF returns "Sufficient". Since no #N/A occurs, IFNA simply passes through the "Sufficient" result.

Common errors

Which IFNA error are you seeing?

Error	Why it happens	How to fix it
#DIV/0!	The first argument performed a division where the denominator was zero, producing a division-by-zero error that IFNA does not trap.	Wrap the risky division in IFERROR or ensure the denominator is never zero before using IFNA.
#REF!	A reference in the value argument pointed to a column that had been deleted, causing a reference error that IFNA leaves untouched.	Update the formula to point to a valid range or restore the missing column before applying IFNA.
#VALUE!	The value argument attempted to add a text string to a numeric cell, resulting in a type-mismatch error that IFNA does not replace.	Convert the text to a number with VALUE() or clean the data so only compatible types are used, then apply IFNA.

Tips and when to use something else

Use IFNA when you specifically want to handle #N/A from lookup functions while letting other errors surface.
Combine IFNA with IFERROR if you need to catch both #N/A and any other error types.
Place IFNA around the smallest possible sub-expression to avoid masking errors you actually want to see.
When you need to handle all errors, not just #N/A, prefer IFERROR instead of IFNA.

Related functions

IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.SWITCHSWITCH evaluates an expression and returns the matching result value, or a default if provided, simplifying multi-branch logic in a single formula.

Frequently asked questions

Why does IFNA not catch a #DIV/0! error?: IFNA is designed to replace only the #N/A error, which typically comes from failed lookups. Other error types, such as division-by-zero, pass through unchanged so you can see the real problem. Use IFERROR if you need to trap those as well.
Can IFNA be used with array formulas?: Yes, IFNA works with dynamic arrays in both Excel and Google Sheets. When the value argument returns an array that contains #N/A in some cells, IFNA replaces each #N/A with the provided fallback while leaving other values intact.
What is the difference between IFNA and IFERROR?: IFNA only intercepts the #N/A error, preserving other errors for debugging. IFERROR catches any error type, including #VALUE!, #REF!, #DIV/0!, etc. Choose IFNA when you only want to hide missing-lookup results and keep other errors visible.
How does IFNA behave when the fallback value is itself an error?: If the value_if_na argument evaluates to an error, IFNA returns that error. The function does not evaluate the fallback for correctness; it simply substitutes whatever you provide, so ensure the alternate value is a valid, non-error result.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator