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

Change #N/A and replace it with alternate values like Zero or Blank.

Description

The Excel IFNA function evaluates the given criterion (formula) and resolves the return value if N/A error then it replaces with a different custom value (value_if_na).

So if the final result value is a n/a expression then you can add the IFNA function to change the end value. 

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) after 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, or INDEX & MATCH, etc. returns NA 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 calculations or formulas.
  • If Value or Value_if_error is an empty cell, IFNA treats it as an empty string value (“”).
  • To manage other than #NA check IFERROR function

Let’s understand with two examples:

Suppose that we use any formula in this case with VLOOKUP and get #N/A as no match found (not available), to replace it with the result we include the IFNA function in a formula.

If you want to change the final expression result to Zero or Blank you can add the IFNA function.

=IFNA(VLOOKUP(C8,$F$6:$G$8,2,FALSE),””)  – Blank instead of #n/a

=IFNA(VLOOKUP(C8,$F$6:$G$8,2,FALSE),”0″) – return 0 instead of n/a

Like here in the second example, If cell D7 the final result is “NO Discount” text as there is no value available in the table that can return a relevant result.

=IFNA(VLOOKUP(C8,$F$6:$G$8,2,FALSE),”NO Discount”) – For Words & Characters

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 »
Returns a different result if the first argument evaluates to an error

IFERROR

How to change the Excel formula result like #DIV, #VALUE!, #REF!, #NAME?, #NUM! by another value like string, blank, and zero with the IFERROR function.

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 »