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.
VLOOKUP function in Excel to lookup and retrieves data from a specific column in the table.
|=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])|
ISNA function used for checking the #N/A error while calculation by using any function.
IF function works on logic_test and returns values based on the result of the test.
|=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.
Write the formula in C2 cell of SHEET 2.
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.
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.
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?
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.