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.
=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 from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Product | Stock |
| 2 | TEA-01 | Green tea 50g | 34 |
| 3 | TEA-02 | Earl Grey 100g | 0 |
| 4 | COF-01 | Espresso beans 1kg | 12 |
| 5 | COF-02 | Decaf ground 250g | 7 |
Result: 12
COF-01 exists, so IFNA passes through its stock of 12.
Result: Unknown SKU
COF-09 is not in the list, so #N/A becomes "Unknown SKU".
Result: 2
MATCH finds TEA-02 in position 2. A missing SKU would show 0.
Result: #DIV/0!
IFNA ignores other errors, so dividing by zero still shows #DIV/0!.
How to use IFNA
- Write your lookup formula.
- Type =IFNA( in front of it.
- Add a comma and the value to show when nothing is found.
- 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
IFERRORFallback when a formula errorsVLOOKUPVertical lookupMATCHPosition of a valueXLOOKUPModern lookup in any direction
Updated