# What is the difference between IFERROR and IFNA in Excel?

IFNA replaces only #N/A, the "not found" error; IFERROR replaces every error. Wrap lookups in IFNA so real mistakes still show: =IFNA(VLOOKUP(…),0)*B2.

**Excel:** `=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2` (Excel 2013 and later)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| SKU | Qty | Line total |  | SKU | Price |
| P-101 | 4 |  |  | P-101 | 39.99 |
| P-109 | 2 |  |  | P-102 | 249 |
| P-104 | two |  |  | P-103 | 89.5 |
| P-102 | 1 |  |  | P-104 | 449 |
| P-103 | 3 |  |  |  |  |

Formula in C2, filled to C6: `=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2`

Result: $159.96; $0.00; #VALUE!; $249.00; $268.50

## How it works

P-109 isn't in the price list, so VLOOKUP returns #N/A and IFNA turns it into 0. Row 4 has the quantity typed as the word "two", so the multiplication fails with #VALUE!, and IFNA leaves that error showing. That is the point: a bad quantity is a data problem to fix, not a missing product. IFERROR would hide both behind 0.

## Other ways

- `=IFERROR(VLOOKUP(A2,$E$2:$F$5,2,FALSE)*B2,0)`: IFERROR around the whole calculation hides the bad quantity too. (Row 4 shows 0 instead of #VALUE!, hiding the typo.)
- `=XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5,0)*B2`: XLOOKUP's fourth argument is a built-in IFNA: it covers "not found" only.

## Common mistakes

- IFERROR around a whole calculation hides typos, deleted columns (#REF!) and text in number cells. Wrap only the part that can legitimately fail.
- IFNA needs Excel 2013 or later. Google Sheets has it too.

## Questions

**When is IFERROR the right choice?**

When every possible error means the same thing, as in `=IFERROR(B2/C2,0)`, where the only error is dividing by zero.

**How do I test which error a cell has?**

`ISNA` is TRUE only for #N/A, `ISERR` for every other error, and `ISERROR` for all of them.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/iferror-vs-ifna