IFNA

What it does

IFNA(value, value_if_na)

IFNA(value, value_if_na) returns value unless value is the specific error #N/A, in which case it returns value_if_na.

The test is on one error code, not on error-ness. #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL! and the extended-family codes all pass straight through unmasked. That single sentence is the whole reason the function exists.

Why it matters: #N/A is the conventional "no match" result of the lookup family (chapter 01), and it is the only error in ordinary lookup use that means the data said no rather than the formula is broken. IFNA is therefore the mask that suppresses the expected answer while leaving genuine breakage visible. IFERROR around the same lookup suppresses both, which is how a deleted column or a misspelled range name turns into a plausible-looking blank instead of an error.

Like IF and IFERROR, IFNA is a lazy selector: the fallback is reached only when the primary is #N/A, and the selected value is returned verbatim without coercion.

Arguments

IFNA(value, value_if_na)

• value — required. The expression to test; in practice, the lookup you actually wanted.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected argument carries.

• Primary is #N/A. The fallback is returned.
• Primary is any other error. Returned unchanged — this is the defining behaviour, and the one readers migrating from IFERROR are surprised by.
• Primary is a normal value. Returned verbatim.
• Fallback is itself #N/A. Returned as-is; the mask does not iterate.
• Arrays. Whether the mask applies elementwise to an array primary or to the array as a whole is not settled by any source consulted here; see below.

How to implement

Notes for implementers

1. Match on the code, not the family. The test is equality with the single #N/A code. An implementation that tests "is it a legacy-family error" or "is it a lookup error" is a different function.
2. Do not route IFNA through a shared IFERROR helper parameterised only by fallback. The two differ in exactly one predicate, which makes the shared-helper refactor tempting and makes an accidental widening of the predicate invisible in review.
3. The Missing mapping is observable. Mapping an omitted slot to #VALUE! means IFNA and IFERROR give different answers for the same malformed call. Whichever mapping is chosen, it should be chosen deliberately.
4. Preserve rich payloads on the pass-through path, as with IFERROR.

Rust code

CatalogueEntry {
    id: IFNA,
    spelling: "IFNA",
    notation: Notation::Call,
    arity: CallableArityShape::exact(2),
    parameters: &[Parameter::value("value"), Parameter::value("value_if_na")],
    demand: ArgumentDemand::FallbackOnError,
    kernel: Kernel::Lazy(ifna_selected),
}

/// IFNA catches #N/A alone; every other error is its result.
fn ifna_selected(
    arguments: &mut LazyArguments<'_>,
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    fallback_selected(IFNA, arguments, services, |code| {
        code == WorksheetErrorCode::NA
    })
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• IFERROR is the total mask. The pair is the clearest total-versus-selective contrast in the library, and choosing between them is a real design decision in every lookup formula.
• ISNA is the inspection counterpart; IF(ISNA(x), y, x) is the older idiom, which evaluates x twice.
• ISERR is ISERROR minus #N/A — the complement of what IFNA catches, from the inspection side.
• NA() produces the value IFNA looks for.
• XLOOKUP takes an if_not_found argument, folding this behaviour into the lookup itself; where XLOOKUP is available it usually removes the need for IFNA entirely, and the two should not be stacked.
• IFNA arrived in Excel 2013 and has no Compatibility-category predecessor; the pre-2013 form is the IF(ISNA(...)) idiom.

Named variants

No named variants are recorded yet.