# IFNA

## 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

### 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.

### 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.

### 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