Function guides · Logical
IFNA function
Returns a value you choose when a formula results in
Syntax
=IFNA(value, value_if_na)
| Argument | What it is |
|---|---|
value | The formula to evaluate. |
value_if_na | What to return if that formula gives #N/A. |
What IFNA does
IFNA returns a replacement value when a formula gives the #N/A error, and the formula’s own result otherwise. Every other error passes through unchanged.
That narrowness is the reason to use it. #N/A means “not available”, and lookups return it when a key is missing. Any other error, such as #REF! or #DIV/0!, signals a real problem that you would rather see.
Where IFERROR is a blanket, IFNA is a targeted fix for the case you expect.
You will meet the older pattern in inherited workbooks: =IF(ISNA(VLOOKUP(...)),"Not found",VLOOKUP(...)). It runs the lookup twice and is twice as long. IFNA runs it once. If you are translating such a formula, keep the same replacement text so nothing downstream changes.
Remember that XLOOKUP has its own not-found argument, and that MATCH, VLOOKUP, HLOOKUP and INDEX with a bad position are the usual sources of #N/A. A typed NA() also produces it, which some analysts use deliberately to keep a gap out of a chart.
A related quirk: a cell can return #N/A without any lookup, because the formula NA() produces it on purpose. Analysts use that to keep a gap out of a line chart, since charts skip #N/A but plot a zero as zero. If you meet a column of #N/A that nobody can explain, check for NA() before assuming a lookup is broken.
IFNA examples
1. A lookup that may not find the key
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Price | ||
| 2 | A-10 | 12.5 | Not listed | |
| 3 | B-22 | 34 |
Z-99 is not in the table, so VLOOKUP returns #N/A. IFNA catches only that error and swaps in "Not listed".
2. Other errors still show
| A | B | C | |
|---|---|---|---|
| 1 | Profit | Revenue | |
| 2 | 340 | 0 | #DIV/0! |
Here the divisor is zero, which gives #DIV/0!. IFNA lets it through, which is the point. A different error means something is wrong, not just that a key is missing.
3. A found value passes through
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Price | ||
| 2 | A-10 | 12.5 | 34 | |
| 3 | B-22 | 34 |
When the lookup works, IFNA returns it unchanged. The second argument is never used. B-22 is in the table, so the price comes back.
Common IFNA mistakes
- Using IFERROR around lookups. IFERROR hides #REF! from a deleted column and #NAME? from a typo as well. IFNA catches only the not-found case.
- Using IFNA and expecting it to catch It does not. Use IFERROR when the error type does not matter.
- Using it in Excel 2010. IFNA arrived in Excel 2013. In older versions use IF(ISNA(...),...).
- Returning 0 for missing keys. A zero can be summed into a total. Return text or an empty string when the cell is a label.
- Wrapping a formula that cannot return IFNA around plain arithmetic does nothing. Wrap the lookup only.
IFNA on Excel job tests
"Show Not found when the employee is not in the list" can be done with IFERROR or IFNA. IFNA is the more precise one, because it leaves other errors visible.
Some tasks plant an unrelated error, a divide by zero or a bad reference, and say not to hide other errors or to handle only missing IDs. That wording points at IFNA.
Match the replacement text to the task exactly. "Not found" and "Not Found" are different strings.
Related functions
IFNA questions
- What is the difference between IFNA and IFERROR?
- IFNA catches only #N/A. IFERROR catches every error type.
- Which Excel versions have IFNA?
- Excel 2013 and later, including Microsoft 365.
- Does XLOOKUP need IFNA?
- Not usually, since XLOOKUP has a built-in fourth argument for not found.
- What causes
- Most often a lookup that found no match, or a value typed as NA().
Practice IFNA
Try it in a working grid: the free lesson IFERROR and IFNA: handling errors on purpose (module 18 of Analyst) has checked IFNA exercises.
Practice this function