Cellwise

Logic function

Excel IFNA function

IFNA returns a formula's result, or a value you choose if the result is #N/A. It is made for lookups: missing items get a friendly label, but real mistakes like #REF! or #DIV/0! stay visible.

fx=IFNA(value, value_if_na)

Arguments

value
The formula to check, usually a lookup.
value_if_na
What to return if the formula gives #N/A.

IFNA examples

Every result below was calculated live from this sample sheet.

ABC
1SKUProductStock
2TEA-01Green tea 50g34
3TEA-02Earl Grey 100g0
4COF-01Espresso beans 1kg12
5COF-02Decaf ground 250g7
  1. fx=IFNA(VLOOKUP("COF-01",A2:C5,3,FALSE),"Unknown SKU")

    Result: 12

    COF-01 exists, so IFNA passes through its stock of 12.

  2. fx=IFNA(VLOOKUP("COF-09",A2:C5,2,FALSE),"Unknown SKU")

    Result: Unknown SKU

    COF-09 is not in the list, so #N/A becomes "Unknown SKU".

  3. fx=IFNA(MATCH("TEA-02",A2:A5,0),0)

    Result: 2

    MATCH finds TEA-02 in position 2. A missing SKU would show 0.

  4. fx=IFNA(1/0,"x")

    Result: #DIV/0!

    IFNA ignores other errors, so dividing by zero still shows #DIV/0!.

How to use IFNA

  1. Write your lookup formula.
  2. Type =IFNA( in front of it.
  3. Add a comma and the value to show when nothing is found.
  4. Close the bracket.

Common IFNA mistakes

Expecting IFNA to catch every error

It only handles #N/A. Use IFERROR if you also need to hide #DIV/0! or #VALUE!.

Using it on XLOOKUP

XLOOKUP has its own if_not_found argument, so you rarely need IFNA around it.

IFNA FAQ

Should I use IFNA or IFERROR with VLOOKUP?

IFNA is usually better. A missing value gives #N/A, which IFNA handles, while a broken range or typo still shows its own error so you notice it.

How do I hide #N/A in Excel?

Wrap the formula in IFNA: =IFNA(your_formula,""). The cell shows blank instead of #N/A.

Which Excel versions have IFNA?

IFNA has been available since Excel 2013, so it works in every current version, including Microsoft 365.

Guides that use IFNA

Related functions

Updated 2026-10-31