Contact
12 programs
Explore the site

IFNA Function (Gnumeric)

Logical Beginner Gnumeric

Learn the IFNA function in Gnumeric: Returns value if not a #N/A error, else alternative.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Gnumeric. How formula examples are checked

Validation limits: Expected outcomes were reasoned from the official manual and checksum-verified Gnumeric 1.12.62 source. No native Gnumeric execution or screenshots were performed.

What IFNA does

IFNA lets a worksheet display an alternative value only when its first argument evaluates to the actual #N/A error. All ordinary values and other error classes pass through unchanged. This distinction matters for lookup reports: an unavailable product can be shown as “Not found” without hiding a broken calculation, invalid reference, or division by zero. This is specified in Gnumeric’s Logic manual [R1] and its implementation [R2].

Official syntax, inputs and version scope

=IFNA(x,y)
Position Official argument Required What to enter
1 x Yes A scalar value, cell reference or expression to inspect for #N/A.
2 y Yes A replacement value or expression returned if and only if x evaluates to #N/A.

Both arguments are positional; there is no documented optional argument or default. Gnumeric’s official Logic manual lists both arguments and its source checks the #N/A error class specifically [R1, R2]. The examples use English-language names and a comma as the argument separator, as in the manual. A localized installation may change displayed function-name/argument separators and decimal formats; verify in the actual target installation rather than assuming that copied punctuation works everywhere. The function is documented in the contemporary 1.12 manual; the latest 1.12-series release identified for this review is 1.12.62 (September 21, 2026) [R1, R3]. The official 1.12.62 source archive was checksum-verified and inspected; this is release-specific source evidence, not a native binary test [R2].

Copyable examples with expected results

Enter each formula in a fresh cell. All “Result” entries are expected, based on the official behavior, not observed Gnumeric results.

Formula Expected result Why
=IFNA(NA(),"Not available") Not available NA() generates genuine #N/A; the second argument is returned [R1, R2].
=IFNA(42,"Not available") 42 Ordinary number passes through.
=IFNA("Ready","Not available") Ready Text passes through.
=IFNA(1/0,"Not available") #DIV/0! Division by zero is not #N/A.
=IFNA(NA(),0) 0 Numeric fallback is allowed.
=IFNA(IFNA(NA(),"First"),"Second") First Inner function removes #N/A; the outer receives normal text.

Reproducible missing-price lookup

  1. Enter SKU in A1 and Price in B1.
  2. Enter P101, P102, P103 in A2:A4 and 12, 18, 25 in B2:B4 respectively.
  3. Enter P102 in D2. Put the following in E2:
=IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"SKU not found")

Expected E2: 18. Replace D2 with P999; expected E2: SKU not found. VLOOKUP uses the first column of the supplied table and returns column 2. Explicit FALSE selects exact matching, rather than relying on the lookup function’s approximate-match default [R4]. These worksheet outcomes are expectations pending native Gnumeric verification.

Advanced use: keep distinct error types visible

A data-cleaning report should not replace every error with the same sentence. For example, =IFNA(1/0,"Missing") is expected to preserve #DIV/0!, highlighting a calculation problem. In contrast, =IFERROR(1/0,"Missing") would use the fallback for that error because Gnumeric’s IFERROR handles any error class [R1]. Choose the narrower function when #N/A alone signifies an expected business condition.

For source data, consider a numeric fallback such as =IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),0) only when zero genuinely means “absent”; otherwise the text message protects against treating missing prices as real prices.

Errors, limits and troubleshooting

  • Still seeing #DIV/0! or #REF!? This is expected: IFNA does not catch these errors. Fix the broken expression or choose IFERROR intentionally [R1, R2].
  • Fallback not displayed? Check whether the first argument is the actual #N/A error, not the literal text "#N/A". These are different data types.
  • Unexpected lookup match? Supply FALSE as the fourth VLOOKUP argument for exact matches; confirm the lookup key is in the table’s first column [R4].
  • Argument error? Pass exactly two positional arguments in the syntax documented for your Gnumeric locale [R1].
  • Formula copied between spreadsheet programs? Do not assume text coercion, default lookup behavior or locale punctuation matches without testing in the destination application.

Gnumeric’s Logic documentation identifies IFNA as Excel-compatible [R1]. The broad public function catalogue’s classification can lag current application compatibility; the functional statement is grounded in the Logic manual, not the catalogue’s coarse classification. For Microsoft Excel, separately confirm exact behavior using Microsoft’s own documentation before declaring edge cases identical.

Related Gnumeric guides: IFERROR, ISNA, IF, and VLOOKUP.

Official research references and validation

Validation: Official manual and release-source review plus independent logical checks of the displayed examples. Native Gnumeric execution, screenshots, locale regression and workbook round-trip tests were not performed.