IFNA function
Value
The value to return if it is not a "not available" error.
ValueIfNotAvailableError
The value to return if the value parameter is a "not available" error.
Returns
The first parameter if it is not a "not available" error and the second parameter otherwise.
Returns a value unchanged if it is not an #N/A (not available)
error and a different value otherwise. IFNA(NA(), 4)IFNA(NA(); 4) returns 4, because NA returns an #N/A
error.
#N/A errors
Lookup functions like XLOOKUP return #N/A errors
("not available") when a value cannot be found. The ISNA function may be used
to detect the #N/A error specifically.
This formula returns TRUE, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP:
The IFNA function is used to return a specific value if a value is an
#N/A error, or the value itself otherwise. This formula returns
500, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given
to XLOOKUP:
ISERR is the
opposite of ISNA, meaning that it detects all errors except for
#N/A errors. Consider this formula:
The formula above returns TRUE if the value of Field2 is 0 or blank,
resulting in a #DIV/0! error ("division by zero"), but it
returns FALSE if the value resulting from the Field1 / Field2Field1 / Field2 cannot be found in the
lookup array.
Unlike IFNA and IFERR, IFERROR detects all errors. If the intent is to detect
#N/A errors specifically, using IFERROR is a mistake.
For an example of why using IFERROR instead of IFNA can be a mistake, consider this formula:
If the value of Field2 is zero, or is blank, this will cause a
#DIV/0! ("division by zero") error to be returned from the
/
operator, which XLOOKUP will pass on to IFERROR, which in
turn will cause IFERROR to return 500 instead of the #DIV/0!
error.
If the intent is to only return 500 if the sought value cannot be found in
the lookup array, it is better to use the formula IFNA(XLOOKUP(10, { 20, 30 },
{ 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 };
{ 200; 300 }); 500) and let the #DIV/0! error
slip past IFNA.
IFNA and arrays
Invoking IFNA with an array as its first parameter applies it to the array as a whole, not to its constituent elements.
This formula returns the array { 99 }{ 99 }, as the XLOOKUP function returns
an #N/A error as the value 3 cannot be found in the { 1, 2 }{ 1; 2 } lookup array:
(The XLOOKUP variant used above is the second one on this page, which takes a formula fragment as its first parameter. Only that variant supports returning arrays.)
Use MAP to invoke IFNA once for every array element instead. This formula returns the array { 1, 2, -1, 4 }{ 1; 2; -1; 4 }:
Above, IFNA is invoked once for each array element. When it encounters the
third array element, which is an #N/A error, it returns -1
instead of the array element. MAP incorporates all return values from IFNA
into the result array.
Examples
Returns -1, because XLOOKUP returns an #N/A
error, as the value 3 cannot be found in the {1, 2 }{1; 2 } array.
Returns 500, because the sought value, 10, is not part of the
{ 20,
30 }{ 20;
30 } lookup array given to XLOOKUP. IFERROR catches all errors,
though, not just the #N/A error returned from XLOOKUP when
it cannot find a value.
Returns 500, because the sought value, 10, is not part of the
{ 20,
30 }{ 20;
30 } lookup array given to XLOOKUP. The IFNA function catches
only #N/A errors, unlike IFERROR, which catches all errors.
If the intent is to only return 500 if the value cannot be found, it is
better to use IFNA.
Returns the array { 99 }{ 99 }, as the XLOOKUP function returns an error on account of the element 3 not being present in the first array.
Returns the array { 1, 2,
-1,
4 }{ 1; 2;
-1; 4 }. IFNA is
invoked once for each array element and returns all array elements
unchanged, except for the third array element. That element is an
#N/A error, and causes IFNA to return -1. MAP incorporates
all return values from IFNA into the result array.