Return a custom result when a formula generates a #N/A error
=IFNA(value, value_if_na)
Return a custom result when a formula generates a #N/A error, otherwise returns the result of the formula.
IFNA is a simpler way to trap and handle errors than more complicated nested IF functions.
IFNA syntax requires a value to test and a value to return if there is a #N/A error. The value argument can be a value, reference or formula.
IFNA examples.
In the examples below, the IFNA function is used to determine whether cells or VLOOKUP formula contain an error and return a custom value.
Use the IFNA function to return a custom value if a formula or cell contains a #N/A error.
Syntax
IFNA(value, value_if_na)
The IFNA function requires 2 arguments:
- value. Required. The argument that is checked for an error.
- value_if_na. Required. The custom value to return if the formula evaluates to a #N/A error.
Try it now!
- Select the answer from the drop down lists in the grey cells.
Scope
IFNA can be used on its own to determine whether a cell contains an error or combined within a formula to test a value prior to performing additional operations. IFNA is useful for trapping and handling #N/A errors generated with lookup functions such as MATCH, VLOOKUP, HLOOKUP and XLOOKUP.
|
=IFNA(C2,"Error found") || returns "Error found" if C2 contains the #N/A error. |
|
=IFNA(VLOOKUP(myValue,myRange,2,0),"Value not found") || returns "Value not found" if the VLOOKUP function returns #N/A. |
Caveats
- IFNA only traps and handles the #N/A error type. For other error types such as #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, use the IFERROR function
- If value is empty, it is handled like an empty text string ("") not an error.
- If value_if_na is empty, no message is displayed when an error is detected.
- IFNA will return an array of results with one item for each value in an array formula.
Related Functions
The following functions are also useful for error handling.
- IFNA to trap and handle #N/A errors specifically.
- IFERROR to trap and handle all error types.
- ISBLANK returns the logical value TRUE if a cell is blank or empty.
- ISERR returns the logical value TRUE for any error type except the #N/A error.
- ISERROR returns the logical value TRUE for any error type.
- ISLOGICAL returns the logical value TRUE if the cell contains either a TRUE or FALSE statement.
- ISNA returns the logical value TRUE if a cell contains the #N/A error
- ISNONTEXT returns the logical value TRUE if the cell contains any value that is not text.
- ISNUMBER returns the logical value TRUE if the cell contains a number.
- ISREF returns the logical value TRUE if the cell contains a reference.
- ISTEXT returns the logical value TRUE if the cell contains any text.
Functions Category