How to use IF ISNA and VLOOKUP function in Excel?

In this article, we are going to learn how to use ISNA function with VLOOKUP to ignore the #N/A error while calculating the formula.

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

And VLOOKUP function is used for retrieving the data information from one data to another data.

If we just want to check #N/A error we can use ISNA function but if we want to mention any output when #N/A error is occurred then we have to use IF function along with ISNA function.

Let’s take an example and understand how we can ignore VLOOKUP #N/A error in Excel.

We have data in Sheet 1 from which we want to update the information in another data and sheet 2 where we want to update the data.

 

Data 1:-

 

image 1

 

Data 2:-

image 2

 

In sheet 2, we want to update the birth date from the sheet 1. TO get the desired output, we need to follow below given steps and formula:-

  • Enter the formula in cell C2 of sheet 2.
  • =IF(ISNA(VLOOKUP(A2,Sheet1!A:F,6,FALSE)),”NotFound”, VLOOKUP(Sheet2!A2,Sheet1!A:F,6,FALSE))
  • Press Enter.
  • Copy the same formula and paste in the range.
  • Birth date will get updated and also where the data is not available in the sheet 1 formula will return “NotFound” as output.

image 3

 

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