How to use IF ISNA and VLOOKUP function in Excel?

In this article, we will learn how to use Excel ISNA function with VLOOKUP to ignore the #N/A error while calculating the formula in MS Excel 2016.

Excel VLOOKUP function to look up and retrieves data from a specific column in the table. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right.

How to use the Vlookup function in excel?

VLOOKUP function in Excel to lookup and retrieves data from a specific column in the table.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

How to use the ISNA function in excel?

ISNA function used for checking the #N/A error while calculation by using any function.

Syntax:

=ISNA(Formula)

How to use the if function in excel?

IF function works on logic_test and returns values based on the result of the test.

Syntax:

=IF(Logic_test, [Value_if_True], [Value_if_False])

Let’s get this with an example.

Here we have two sheets, we have to get the BIRTHDT from SHEET 1 table to SHEET 2 Table using the functions.

SHEET 1:
Sheet 1
SHEET 2:
Sheet 2

Write the formula in C2 cell of SHEET 2.

=IF(ISNA(VLOOKUP(A2,Sheet1!A:F,6,FALSE)),”NotFound”, VLOOKUP(Sheet2!A2,Sheet1!A:F,6,FALSE)

Explanation:
Vlookup function in excel looks for the 6th column of the SHEET 1 matching  REC_ID in SHEET 1 from SHEET 2.
ISNA function in excel looks for the #NA error and passes it on to IF function.
IF formula in excel checks if any #NA error occurs, it print “NotFound” instead of the #NA error.

Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl + D.
Sheet 3

As you can see in the above image, the formula is working fine. And the user can work without getting irritated by #NA error.

Hope you understood IF, ISNA and VLOOKUP function in Excel 2016, 2013 and 2010. There are more articles on VLOOKUP Examples and HLOOKUP Examples here.   Please do check more links here and if you have any unresolved query, please state that in the comment box below. We will help you.

 

Below are the examples to learn more:-

Two-way table lookups – can’t get the function to work

Question asked by user:-

If anybody can help with this I’m trying to get the correct price from the table from the data entered in AB2 and AB3. The function used is in cell AB7.

Click here:- How to use ISNA function along with INDEX and MATCH function?

 

IF Statement combining multiple ‘IF TRUE’ conditions

Question asked by user:-

Assuming that I have a worksheet with columns A,B,C and D on it, now I want an IF statement in column E which follows this argument:
IF A1 is “0″ or “N/A”, then show N/A, but if A1 is any other number, then B*(C+D)
I hope that’s easy to follow. I have tried so hard to get this to work but just can’t.
As ever, any help would be hugely appreciated.

Click here:- How to use ISNA with nested IF condition?

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube