How to Use IfNa in VBA (With Example)

You can use the IfNa method in VBA to return a specific value if a formula results in a #N/A error.

Here is one common way to use IfNa in practice:

Sub UseVLOOKUP()
    With Application
    Range("F2").Value = .IfNa(.Vlookup(Range("E2"), Range("A2:C11"),3,False), "No Value Found")
    End With
End Sub

This particular macro attempts to use the VLOOKUP function to look up the value in cell E2 in the range A2:C11.

If the value in cell E2 cannot be found, then the formula would result in #N/A.

However, we can use the IfNa method to return “No Value Found” instead of #N/A.

The following example shows how to use this syntax in practice.

Example: How to Use IfNa in VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to look up the team name “Kings” in the dataset and return the corresponding value in the assists column.

We can create the following macro to do so:

Sub UseVLOOKUP()
    With Application
    Range("F2").Value = .IfNa(.Vlookup(Range("E2"), Range("A2:C11"),3,False), "No Value Found")
    End With
End Sub

When we run this macro, we receive the following output:

The macro correctly returns a value of 3 assists for the Kings.

Now suppose we change the name of the team in cell E2 to “Grizzlies”, which does not exist in the dataset.

When we run this macro again, it will return “No Value Found” since this is the value that we specified should be returned if the VLOOKUP function results in a #N/A error.