IFNA
Replaces the #N/A error with your own value. Use it when VLOOKUP or MATCH didn’t find the key but other errors mustn’t be hidden.
IFNA(value, value_if_na)- value
- A lookup formula, for example VLOOKUP(A2,$E$2:$F$5,2,0).
- value_if_na
- What to show if the result is #N/A: text in quotes, a number or "".
Return value
The value of the first argument if it isn’t #N/A. Instead of #N/A the function returns the second argument, and it leaves other errors, for example #DIV/0!, as they are.
Examples
Price from the price list
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
SKU
Price, $
SKU
Price, $
A-101
A-101
1290
B-220
A-102
990
A-102
C-310
2450
C-310
D-400
640
Sheet1
Details
IFNA works like IFERROR but reacts to only one error: #N/A. Lookup functions return it when the value you’re looking for isn’t in the table.
Other errors, for example #REF! from a wrong column number, IFNA leaves visible. That way you won’t miss a problem in the formula itself.
Common mistakes
- The key actually is there, but with an extra space or stored as text. IFNA shows “Not in list” even though the problem is in the data.
- The function is available in Excel 2013 and later, and in Microsoft 365. In older versions, use IFERROR.