IFNA

Substitute #N/A error and replace with an alternate value in Excel
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression

Rational

Substitute #N/A error and replace with an alternate value.

Description

The Excel IFNA function evaluate given criterion (value) in case the return value is a #N/A error then it substitutes with a different text string (value_if_na).

Syntax

=IFNA (value, value_if_na)

Parameters

value – The criteria to evaluate
value_if_na [optional] – A text string if criteria return an #N/A error

Return Value

The text string in case of #N/A error otherwise result of criteria

Key Notes

  • If the criteria (logical_test) aftere valuation return an #N/A error, only then second argument trigger and return the text string given in the second argument [value_if_na].
  • Lookup formula like VLOOKUP, HLOOKUP, etc. returns #N/A in case of no match in the lookup range, IFNA function can avoid such error result.
  • IFNA function is useful to avoid #NA error value to display which may occur while evaluating other calculation or formula.
  • If Value or Value_if_error is an empty cell, IFNA treats it as an empty string value (“”).

Example

Suppose that we use any formula and get #N/A as no match found, to replace it with value we include IFNA function in a formula like here in cell D7 it return NO Discount and in cell D8 it return 10.0% we put formula =IFNA(VLOOKUP(C8,$F$6:$G$8,2,FALSE),”NO Discount”)

 

Keep Reading Similar Functions:

Return value based on given criteria in Excel

IF

The IF function is a handy logical operator that return value based on a valid test of criteria, if it matches (TRUE) and another value if it does not.

Read More »
Substitute the error result and replace with an alternate value in Excel

IFERROR

The IFERROR function evaluates a criterion in case of an error then it substitutes with a different text string. I.e.,=IFERROR(2/0,”Error”) return “Error”.

Read More »
Check multiple criteria and return the first match in Excel

IFS

Rational Check multiple criteria and return the first match. Description The Excel IFS function evaluate multiple criteria at once and return the first TRUE criteria

Read More »