How To Use The Excel Is Functions, Excel Isna Function

This error occurs when another formula in Excel cannot find what we are asking it to find.

Đang xem: Is functions

It is not a sign that there is anything wrong with the formula itself.

The ISNA function provides a simple, yet elegant, way to handle the #N/A error.

It allows the user to substitute a different value in place of the error. This can be a custom value such as a text string.

Let’s get into the details…

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of lingocard.vn

Table of Contents

Syntax

The syntax for ISNA is about as basic as you can get. It requires a single argument, value.

The syntax of the ISNA function

=ISNA(value)

However, the value argument is another formula itself. Therefore, the complexity of your ISNA depends on the situation.

On its own, the ISNA function will return one of two possible values.

It will return either a TRUE or FALSE value based on whether the value argument turns out to be the #N/A error.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

hướng dẫn it right below!

hướng dẫn the FREE Exercise File

hướng dẫn free exercise file

Example 1: basic TRUE or FALSE result

One of the more common situations you will run into the #N/A error is when using various lookup functions.

For instance, if you have a VLOOKUP that cannot find a match to the value it is trying to look up, it will return this error.

As we alluded to earlier, this is not a flaw in the VLOOKUP formula itself. It is simpye a matter of its design.

With ISNA, the developers at Microsoft have provided a more elegant way to handle this error.

So let’s look at this in a practical example.

In the following figure, we have a product table containing a code and matching quantity on hand.

We have build a lookup table using the VLOOKUP to return the quantity on hand for the product code we enter.

We will wrap our VLOOKUP formula in an ISNA function so we can test it for the error.

Kasper Langmann, Co-founder of lingocard.vn

When we select a product that exists in the lookup table, the ISNA function evaluates to FALSE.

However, when we enter a value that does not exist in our lookup table, the ISNA function evaluates to TRUE.

This is because the VLOOKUP will result in the #N/A error.

Example 2: creating something a bit more elegant than TRUE and FALSE

If we recruit the help of a well-placed IF function, we can leverage the TRUE/FALSE nature of the ISNA results.

Xem thêm: Khái Niệm Nghị Luận Trong Văn Bản Tự Sự, Giáo Án Bài Nghị Luận Trong Văn Bản Tự Sự

This will allow us to substitute a custom text string for either outcome.

Let’s use the same VLOOKUP and product table as we used in Example 1.

We will examine how we can use an IF function to return the text string “Available” when we get FALSE from the ISNA.

If it results in a TRUE outcome, we will have the IF function output “Invalid Product Code.”

Kasper Langmann, Co-founder of lingocard.vn

Note that you can insert a cell reference for the value argument in the ‘ISNA’ formula.

This is what we have done in our IF formula in cell C15 for Remarks.

Depending on the scenario, you can combine the ISNA function with othersto generate some useful results.

Example 3:even more elegant results when combining ISNA with other functions

We showed you how you can generate custom text strings by combining ISNA with VLOOKUP and IF.

In this next example, we are going to take it a step further and use the actual results of a VLOOKUP with a text string.

Of course, we will also generate a custom message to address the #N/A error should it occur.

We have a table that contains room types and the quantity available for use. If the user requires a certain type of room, they can enter it into the Required Room Type field.

Our formula in the Availability field will tell the user how many rooms are available.

If it is not a valid room type, it will tell them to select a valid room type.

If we select room type D, we get the following result.

If we insert room type R, we will get the following result.

So how did we do this? This is another way to leverage ISNA when used in a larger formula to generate different outputs.

Kasper Langmann, Co-founder of lingocard.vn

We will place a bare bones VLOOKUP in cell F6. Then substitute that cell reference to make things a bit easier to read.

This is definitely a long formula.

Yet, the important thing to note is that this is the same concept as our previous example. The difference here is that we have used some concatenation to create a text string.

This string includes the resulting value from the VLOOKUP when there is no #N/A error.

Conclusion

The ISNA function is simple in concept and design. But often, this is exactly what can make a function so powerful.

As you can see after a few examples, the ISNA makes for elegant handling of the #N/A error.

We can also integrate ISNA into a larger formula. This allows us to generate custom outputs can that are both elegant and relevant.

Xem thêm: Trình Bày Phương Pháp Khám Thận Cho Vật Nuôi, Hướng Dẫn Dành Cho Các Bác Sĩ Thú Y

Kasper Langmann, Co-founder of lingocard.vn
Contact Information

lingocard.vn ApS

Address: Otto Brandenburgs Vej 58, 3.tv, 2450 København SV, Denmark.

VAT-ID: DK-40428631

lingocard.vn

– About us

– Contact us

– Excel consultant

Customer Service

– Customer support

– Terms and Conditions

– Login

Courses

– Zero to Hero: Become an Excel-Superuser in 14 hours

– VBA Masterclass: Become a VBA-Pro in 20 hours

– Power BI Essentials: Learn Power BI in 12 hours

– Team Solution: For Businesses and Organizations

Our Best Free Content

We’ve published +100 Excel-tutorials on our blog. Here are our top 3 picks:

1:The last guide to VLOOKUP you’ll ever need

2: How to Delete Blank Rows Easily

3: INDEX+MATCH with multiple criteria in 5 easy steps

Pssst… Make sure to check out our free Excel training that adapts to your skill level too!