說明
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.
語法
IFNA(value, value_if_na)
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
值 |
是 | 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 |
是 | The value to return if the expression evaluates to #N/A. | Any value, including text, numbers, or logical values. |
範例
範例資料
| A | B |
|---|---|
| Apple | $1.50 |
| Banana | $0.75 |
| Cherry | $2.25 |
| 日期 | $3.00 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=IFNA(VLOOKUP("Banana",A1:B4,2,FALSE), "Not in stock") |
Looks up the price of "Banana". If found, returns the price; if not, returns "Not in stock". | 0.75 |
=IFNA(VLOOKUP("Grape",A1:B4,2,FALSE), "Not in stock") |
Looks up the price of "Grape". Since it's not in the list, returns "Not in stock". | Not in stock |
=IFNA(INDEX(B1:B4,MATCH("D*", A1:A4, 0)), "No fruit starting with 'D'") |
Looks to find a fruit value starting with 'D'. Returns its price if found, otherwise "No fruit starting with 'D'". | 3.00 |
=IFNA(1/0, "Cannot divide by zero") |
Attempts to divide by zero. This results in a #DIV/0! error, not #N/A, so IFNA doesn't catch it. | #DIV/0! |
附註
- 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.)
提示
- 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.