totn Excel Functions

MS Excel: How to use the IFNA Function (WS)

This Excel tutorial explains how to use the Excel IFNA function with syntax and examples.

Description

The Microsoft Excel IFNA function returns an alternate value if a formula results in #N/A error.

The IFNA function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the IFNA function can be entered as part of a formula in a cell of a worksheet.

Microsoft Excel

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the IFNA function in Microsoft Excel is:

IFNA( formula, alternate_value )

Parameters or Arguments

formula
The formula or value that you want to test.
alternate_value
The alternate value that is returned if the formula results in #N/A error value. Otherwise, the function will return the result of the formula if no #N/A error occurs.

Returns

The IFNA function returns any datatype such as a string, numeric, date, etc.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel IFNA function examples and explore how to use the IFNA function as a worksheet function in Microsoft Excel:

Microsoft Excel

The IFNA function is helpful when creating formulas using functions that can return the #N/A error such as VLOOKUP, HLOOKUP or LOOKUP. In these cases, you can use the IFNA function to return an alternate value instead of the #N/A error value.

Based on the Excel spreadsheet above, column F contains a VLOOKUP formula to find the Unit Price for the product name found in column E.

Column G uses the IFNA function to return an alternate value of 0 when the VLOOKUP formula results in an error.

For example, the IFNA function in cell G3 would return a value of 0 (ie: the alternate value) because the VLOOKUP formula VLOOKUP(E3,$A$3:$C$7,2,FALSE) results in the #N/A error:

=IFNA(VLOOKUP(E3,$A$3:$C$7,2,FALSE),0)
Result: $0.00

However, the IFNA function in cell G4 would return $14.00:

=IFNA(VLOOKUP(E4,$A$3:$C$7,2,FALSE),0)
Result: $14.00

Because VLOOKUP(E4,$A$3:$C$7,2,FALSE) does not result in #N/A error, the function would return the result of the formula which is $14.00.

The IFNA is an amazing function that can be used to trap and handle errors in your Excel formulas.