How to remove #N/A error in vlookup

How to remove #N/A error in vlookup .

It is very irritating when you may be using vlookup formula in excel and you will get error like #N/A. You may be wondering how to remove #N/A error in vlookup . This article will help you to resolve this issue.

For users who are expert in excel , this is small thing however it creates issue in creating report if you face such error and you don’t know the solution. You may not be able to use SUM formula to do total of column as some of cells may not have corresponding values which you have tried to fetch using vlookup . Below Screen shot may be same as your error of #N/A.

 

 

 

 

 

Please see above example . (you can click on image to check full view). For material code 20 ,no corresponding material code and name available in source data i.e in column A and B. That is why you are getting error . When you are working on thousand rows , it is very difficult to work on data if you face such error .

Solution :

It is very easy to resolve this error. you have to use “IFERROR” formula within VLOOKUP function .

Example :

=IFERROR(VLOOKUP(E2,$A$2:$B$6,2,0),0)

Please refer below screen shot for better understanding. Error mention in first image is resolved using IFERROR formula.

How to remove #N/A error in vlookup
How to remove #N/A error in vlookup

 

 

 

 

 

This IFERROR is not limited to vlookup, you can you this formula for any such similar error like #DIV/0! error.

#DIV/0! error

Solution:

Please refer below screen for resolving below error of #DIV/0! error.  There is no value available in cell No. A2, excel is showing this error.

Solution to DIV Error

 

Hope above post is useful in answering your query regarding How to remove #N/A error in vlookup. Do follow our website and give your valuable feedback in comment section or you can use contact us section .

Print Friendly, PDF & Email

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.