Description
Use this function to return a specified value if an expression evaluates to #N/A; and if it doesn't, return the result of the expression.
Syntax
IFNA(value, value_if_na)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
value |
Yes | The value or expression to check for an #N/A error. | A number, text, or logical value, a reference to a cell containing any of these, a cell range that includes any of these, or a formula which results in any of these. |
value_if_na |
Yes | The value to return if the expression evaluates to #N/A. | Any value, including text, numbers, or logical values. |
Examples
Sample data
| A | B | |
|---|---|---|
| 1 | Apple | $1.50 |
| 2 | Banana | $0.75 |
| 3 | Cherry | $2.25 |
| 4 | Date | $3.00 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Search for a value in a range and return a corresponding value from another column, and if the lookup value isn't found, provide a custom message. | =IFNA(VLOOKUP("Banana",A1:B4,2,FALSE), "Not in stock") |
This formula works in the following manner:
For this data set, the formula returns 0.75. |
| Search for a value in a range and return a corresponding value from another column, and if the lookup value isn't found, provide a custom message. | =IFNA(VLOOKUP("Grape",A1:B4,2,FALSE),"Not in stock") |
This formula works in the following manner:
For this data set, the formula returns Not in stock. |
| Search for a value in a range that starts with a specific letter and return the value from the associated column; if the lookup value isn't found, provide a custom message. | =IFNA(INDEX(B1:B4,MATCH("D*",A1:A4,0)),"No fruit starting with 'D'") |
This formula works in the following manner:
For this data set, the formula returns 3.00. |
| Perform a division operation; if a "divide by zero" error occurs, provide a custom message. | =IFNA(1/0,"Cannot divide by zero") |
This formula works in the following manner:
For this data set, the formula returns #DIV/0!. |
Notes
- IFNA only catches #N/A errors. (These indicate that the data is not found.)
- If a formula doesn't result in a #N/A error, IFNA returns that value. If the formula returns a different error, IFNA returns that error value.
- For #N/A errors, IFNA allows you to specify an alternative value to be displayed if that error is returned.
- IFNA is particularly useful with VLOOKUP, HLOOKUP, MATCH, and INDEX functions, which often return #N/A when a value is not found.
- IFNA can be nested within other functions or used as part of a larger formula. (See examples above.)
Tips
- Use IFNA when you specifically want to handle #N/A errors but allow other types of errors to show.
- IFNA is more efficient than using
IF(ISNA(…))because it only evaluates the expression once. - For handling multiple error types, consider using IFERROR instead.
- When using IFNA with lookup functions, consider whether returning a default value for missing data is appropriate for your use case.
- IFNA can be useful in financial models where distinguishing between a true zero value and a "not found" scenario is important.