# IFNA function

IFNA(Value, ValueIfNotAvailableError) IFNA(Value; ValueIfNotAvailableError)

## 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:

ISNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }))ISNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }))

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:

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

ISERR is the opposite of ISNA, meaning that it detects all errors except for `#N/A` errors. Consider this formula:

ISERR(XLOOKUP(Field1 / Field2, { 20, 30 }, { 200, 300 }))ISERR(XLOOKUP(Field1 / Field2; { 20; 30 }; { 200; 300 }))

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:

IFERROR(XLOOKUP(Field1 / Field2, { 20, 30 }, { 200, 300 }), 500)IFERROR(XLOOKUP(Field1 / Field2; { 20; 30 }; { 200; 300 }); 500)

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:

IFNA(XLOOKUP(Element = 3, { 1, 2 }, { { 10 }, { 20 } }), { 99 })IFNA(XLOOKUP(Element = 3; { 1; 2 }; { { 10 }; { 20 } }); { 99 })

(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 }:

MAP({ 1, 2, NA(), 4 }, IFNA(Element, -1))MAP({ 1; 2; NA(); 4 }; IFNA(Element; -1))

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

IFNA(XLOOKUP(3, { 1, 2 }, { 10, 20 }), -1)IFNA(XLOOKUP(3; { 1; 2 }; { 10; 20 }); -1)

Returns -1, because XLOOKUP returns an `#N/A` error, as the value 3 cannot be found in the {1, 2 }{1; 2 } array.

IFERROR(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFERROR(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

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.

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

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.

IFNA(XLOOKUP(Element = 3, { 1, 2 }, { { 10 }, { 20 } }), { 99 })IFNA(XLOOKUP(Element = 3; { 1; 2 }; { { 10 }; { 20 } }); { 99 })

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.

MAP({ 1, 2, NA(), 4 }, IFNA(Element, -1))MAP({ 1; 2; NA(); 4 }; IFNA(Element; -1))

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.