How and Where to use the ISNA Function with IF and VLOOKUP function

After discussing IFERROR() in Microsoft Excel Error Handling in previous post, now we should discuss about ISNA() function.
I would strongly recommend you to use IFERROR() in place of ISNA(), but it is better to have some knowledge about ISNA() function as ultimately its' the situation's demand where to use IFERROR() or ISNA() or ISERROR().

Description

The Microsoft Excel ISNA() function can be used to check for a #N/A (value not available) error Unlike IFERROR(), ISNA() will not mask each and every #error in MS Excel like #VALUE, #NAME, #NULL etc., ISNA() function will mask only #N/A errors in MS Excel.

The ISNA function is a built-in function in Excel that is categorized as an Information Function. The output of ISNA() is either TRUE or FALSE, the two most important logical output, as this is providing information we can categorize this function as information function in Microsoft Excel or Spreadsheet.

Now as the output of this function is logical i.e., either TRUE or FALSE we need to use another logical function IF() to omit the word TRUE or FALSE and printing the matching data (I'll discuss on IF() function latter, a  very important function) because while preparing some data you might not want to display the word TRUE or FALSE in your data. 
Let us look into the syntax of ISNA() function.

Syntax
The syntax for the ISNA function in Microsoft Excel is very simple:
ISNA( value )

Parameters or Arguments

Value
The 'value' means the data that you want to test. If value is a #N/A error value, this function will return TRUE. Otherwise, it will return FALSE. The ISNA() function can be used in two different ways.

a) Either you can give reference the cell to ISNA() function giving you #N/A error like =ISNA(B2) or 

b) You can use it within a function, i.e.,. as nested function like =ISNA(VLOOKUP(I5,B5:E20,2,0))

This function Applies To
               Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Let us have some practical example to understand this function.

Suppose we have two tables Table 1 and Table 2 as shown in the below picture. Now we need to get the value of Table 2 in NAME column corresponding to Employee Code from Table 1, so we use VLOOKUP() function to get the data. As the employee code 10040 and 10111 is not present in Table 1 it is showing us #N/A error (Value not available), we will mask this #N/A by ISNA().

How to use ISNA function in Excel errors

If you analyze the situation carefully (data output by VLOOKUP) and you want to print the text "Name Not Found" in place of #N/A then two things need to be done.

1) Find out where #N/A error comes and as it will return either TRUE or FALSE so 

2) we need to use some logical function to print the text "Name Not Found" in place of TRUE.

Again I would like to remind you that as the output is 'logical value' in ISNA() function, so if you use ISNA() with VLOOKUP() function and will fill the entire column 'NAME' (nested function i.e., a function within a function) then each and every result will be either TRUE or FALSE irrespective of data available or not in the table.

But, we have to be tricky while using this function. Refer to the below image, as the output is given by VLOOKUP() function, is either a Matching Column Data (Lik 'DDD') or a #N/A error, we need to print the matching data and in the same time print the text "Name Not Found" in place of #N/A.

How to use IF and ISNA function in Excel errors

So, what we'll do ? 

We'll insert ISNA() function before VLOOKUP() function and then move to the end of the formula to close the ISNA function by putting a bracket or closing parentheses (e.g., =ISNA(VLOOKUP($G3,$A$3:$D$12,2,0)) ) and now our formula can decide whether the output is giving a #N/A error or not by printing TRUE or FALSE. The first part is complete.

For the second part, we have to use IF() function, in brief, the syntax of IF() is =IF(logical_test, value _if_true, value_if_false). Here the whole formula is the logical_test part or parameter in IF() function, hence type IF( after the '=' sign and move to the end of the formula and put a comma IF() will ask for value_if_true, type "Name Not Found" (because ISNA() will return TRUE whenever it gets a #N/A error) including double quote as this a string/text and again put a comma IF() will ask for value_if_false, now the tricky part comes, copy the VLOOKUP() part from the formula and paste it and close the bracket because if ISNA() returns FALSE meaning VLOOKUP() has found the matching data and therefore, we need to print the data. Its' done...

The only drawback of ISNA() is it will show you either TRUE or FALSE as its result but in real life we need more, we need to rectify only that portion of the data which is giving a #N/A error and to make it we have to use another logical function IF(). If the situation is not demanding to use ISNA(), please do not use it, use IFERROR() function, its more productive. 

Why ? Please read the article on IFERROR().

Hope this discussion is helpful to you. Thank you...