Excel IFNA function to handle #N/A errors

IFNA function in Excel

The Excel IFNA function is purposed for catching and handling #N/A errors. If a formula evaluates to #N/A, IFNA traps that error and replaces it with a custom value you specify; otherwise returns a normal result of the formula.

How to use IFNA function in Excel

To effectively use IFNA in Excel, follow this generic approach:

1. In the first argument (value), put a formula impacted by the #N/A error.
2. In the second argument (value_if_na), type the text you want to return instead of the standard error notation. To return an empty cell when nothing is found, supply an empty string ('"").

To return custom text, the generic formula is:

To return a blank cell, the generic formula is:

Let's see how it works on a simple example. In the table below, suppose you want to know how a score of a given student ranks among others. Since the data is sorted by the Score column from highest to lowest, the rank will match the relative position of the student in the table. And to get the position, you can use the MATCH function in its simplest form:

=MATCH(E1, A2:A10, 0)

Because the lookup value (Neal) is not available in the lookup array (A2:A10), a #N/A error occurs.

Running into this error, inexperienced users might think there's something wrong with the formula, and you as the workbook creator will receive a lot of questions. To avoid this, you can explicitly indicate that the formula is correct, it just cannot find the value it is asked to look for. So, you nest the MATCH formula in the first argument of IFNA and, in the second argument, type your custom text, "Not found" in our case:

=IFNA(MATCH(E1, A2:A10, 0), "Not found")

Now, instead of the standard error notation, your own text is displayed in a cell, informing users that the lookup value is not present in the dataset:

How to use IFNA with VLOOKUP

Most often the #N/A error occurs in functions that look for something such as VLOOKUP, HLOOKUP, LOOKUP, and MATCH. The below examples cover a few typical use cases.

IFNA to return multiple results

In case the inner function (i.e. the formula placed in the value argument) returns multiple values, IFNA will test each returned value individually and output an array of results. For example:

=IFNA(VLOOKUP(D2:D4, A2:B10, 2, FALSE), "Not found")

In Dynamic Array Excel (Microsoft 365 and Excel 2021), a regular formula in the topmost cell (E2) spills all the results in the neighboring cells automatically (in terms of Excel, it's called a spill range).

In pre-dynamic versions (Excel 2019 and lower), a similar effect can be achieved by using a multi-cell array formula, which is completed with the Ctrl + Shift + Enter shortcut.

What's the difference between IFNA and IFERROR?

Depending on the root cause of the problem, an Excel formula can trigger different errors such as #N/A, #NAME, #VALUE, #REF, #DIV/0, #NUM, and others. The IFERROR function catches all those errors while IFNA is limited to only #N/A. Which one is better to choose? That depends on the situation.

If you want to suppress any kind of error, then use the IFERROR function. It is especially useful in complex calculations when a formula includes several functions that can generate different errors.

With lookup functions, you'd better use IFNA as it displays a custom result only when a lookup value is not found and does not hide underlying problems with the formula itself.

To illustrate the difference, let's bring back our basic IFNA VLOOKUP formula and "accidentally" misspell the function's name (VLOKUP instead of VLOOKUP).

=IFNA(VLOKUP(E1, A2:B10, 2, FALSE), "Did not take the exam")

IFNA does not suppress this error, so you can clearly see that something is wrong with one of the function names:

Now, let's see what happens if you use IFERROR:

=IFERROR(VLOKUP(E1, A2:B10, 2, FALSE), "Did not take the exam")

Hmm… it says that Olivia did not take the exam, which is not true! This is because the IFERROR function traps the #NAME? error and returns the custom text instead. In this situation, it not only returns wrong information but also obscures the issue with the formula.

Available downloads

Excel IFNA formula examples (.xlsx file)