**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:

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