Logical

IFNA Function in Excel

Returns a value you specify if a formula returns #N/A; otherwise returns the formula result.

Syntax

  • =IFNA(value, value_if_na)

Arguments

  • value (required): Value or formula to check
  • value_if_na (required): Value to return if #N/A

Examples

  • =IFNA(VLOOKUP(A1,B:C,2,0), "Not found") - Handle lookup #N/A - Result: Value or Not found

Excel IFNA function documentation

  • IFNA is a Logical function introduced in Excel 2013 that catches only #N/A errors and returns a substitute value.
  • Syntax: =IFNA(value, value_if_na). Other error types (#VALUE!, #REF!, etc.) pass through unchanged.
  • Ideal for lookup formulas where #N/A means "not found" but other errors signal real formula problems.
  • Related: [IFERROR](/functions/iferror/), [XLOOKUP](/functions/xlookup/) with if_not_found, [ISNA](/functions/isna/).

IFNA syntax and arguments

  • value (required): the formula to test — typically VLOOKUP, XLOOKUP, MATCH, or INDEX/MATCH.
  • value_if_na (required): returned only when value evaluates to #N/A.
  • IFNA does not catch #VALUE!, #REF!, or #DIV/0! — those still display for debugging.
  • Example: =IFNA(VLOOKUP(E2,$A$2:$D$500,4,FALSE), "No match") replaces only #N/A.

Step-by-step: IFNA on a lookup column

  • Step 1 — Write the lookup: =XLOOKUP(G2, product_ids, prices).
  • Step 2 — Confirm #N/A appears only for genuinely missing IDs (not #REF! from bad ranges).
  • Step 3 — Wrap: =IFNA(XLOOKUP(G2, product_ids, prices), "Not in catalog").
  • Step 4 — In M365, compare with =XLOOKUP(G2, ids, prices, "Not in catalog") — same UX, fewer wrappers.
  • Step 5 — Audit: temporarily remove IFNA to verify no hidden #REF! errors remain.

IFNA vs IFERROR decision guide

  • Use IFNA when the inner formula is a lookup and you want other errors visible during development.
  • Use IFERROR when any error should show the same fallback (e.g. optional worksheet links).
  • Use ISNA inside IF for legacy workbooks without IFNA: =IF(ISNA(VLOOKUP(...)), "Missing", VLOOKUP(...)).
  • Fix guide: [#N/A error hub](/errors/na/) · [VLOOKUP returning #N/A](/problems/vlookup-returning-na/).

Worked examples to copy

  • VLOOKUP label: =IFNA(VLOOKUP(A2,Table,2,FALSE), "Unknown").
  • INDEX MATCH: =IFNA(INDEX(C:C,MATCH(A2,A:A,0)), "").
  • Return zero instead of text: =IFNA(XLOOKUP(A2,Keys,Amounts), 0).
  • Combine with IF for blanks: =IF(A2="", "", IFNA(XLOOKUP(A2,IDs,Names), "Not found")).

People also ask

  • What Excel version has IFNA? — Excel 2013 and later on desktop.
  • IFNA vs IFERROR for XLOOKUP? — IFNA preserves #REF! visibility; XLOOKUP if_not_found may replace both needs.
  • Can IFNA return a number? — Yes. value_if_na accepts numbers, text, blanks, or formulas.
  • Why still see #N/A with IFNA? — IFNA wraps the wrong expression, or the error is not #N/A.

Common errors

  • Only catches #N/A, not other errors

Use cases

  • Lookup error handling
  • Clean reports
  • Default values

Frequently asked questions

  • What is the difference between IFNA and IFERROR? IFNA only catches #N/A errors (typically from failed lookups). IFERROR catches ALL errors (#N/A, #VALUE!, #REF!, #DIV/0!, etc.). Use IFNA when you specifically want to handle 'not found' but let other errors show (they might indicate real problems).
  • Should I use IFNA or IFERROR for VLOOKUP? IFNA is often better for lookups because it only handles 'not found' (#N/A). If your VLOOKUP has a #REF! error (bad column index) or #VALUE! (wrong data type), you want to see those errors, not hide them. IFNA lets real errors through.
  • Can I use IFNA with XLOOKUP? You can, but XLOOKUP has a built-in if_not_found parameter: =XLOOKUP(value, range, return, "Not found"). This is cleaner than =IFNA(XLOOKUP(...), "Not found"). Use XLOOKUP's native error handling when possible.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use IFNA

  • Lookup error handling — common Logical scenario for IFNA.
  • Clean reports — common Logical scenario for IFNA.
  • Default values — common Logical scenario for IFNA.

IFNA in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • IFNA syntax: =IFNA(value, value_if_na)
  • value (required): Value or formula to check
  • value_if_na (required): Value to return if #N/A
  • Confirm IFNA arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm IFNA arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IFERROR (/functions/iferror/): Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.
  • ISNA (/functions/isna/): Returns TRUE if the value is the #N/A error.
  • NA (/functions/na-function/): Returns the error value #N/A (value not available).

Common problems and fixes

  • Why Is VLOOKUP Returning #N/A? (/problems/vlookup-returning-na/): Use FALSE for exact match, confirm the lookup value exists in the first column, and clean hidden spaces or mismatched data types.
  • Why Is IFERROR Not Working? (/problems/iferror-not-working/): Wrap the exact expression that produces the error, use IFNA for #N/A only, and fix the source formula before hiding the error message.

Errors to watch for

  • Only catches #N/A, not other errors — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter IFNA on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

IFNA worked examples to copy

  • =IFNA(VLOOKUP(A1,B:C,2,0), "Not found") — Handle lookup #N/A. Expected result: Value or Not found.

IFNA reference summary for crawlers and offline review

  • IFNA belongs to the Logical category in Excel. Returns a value you specify if a formula returns #N/A; otherwise returns the formula result.
  • Full syntax: =IFNA(value, value_if_na). Open /functions/ifna/ for parameters, FAQs, and related pages.
  • Common mistakes: Only catches #N/A, not other errors
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.