Vlookup Errors in Excel – Excel Formula

77

Vlookup Error

Vlooup errors are 3 types  (1) Vlookup  #N/A error, (2) Vlookup #VALUE Error and (3) Vlookup #NAME error . Here in this article we are going to discuss all these vlookup errors in depth and you are going to learn how to fix these errors instantly to get the correct results. Before you go into this article you must know about the what is vlookup function, what is the definition of the vlookup and how to when you use the vlookup once you learn these tricks then you please read this article. Else it is very difficult for you to understand. 

1)  Vlookup #N/A Error :

If you check vlookup syntax, it has 4 variety of ingredients are available in that 1st one is Lookup Value, 2nd Table Array, 3rd column index number and 4th exact match or approximate match

If Lookup value related value is unavailable in the given range then this error can be thrown by Vlookup formula. When you get #N/A in the vlookup, you have to check whether you have the given correct lookup values or any misspelled in the lookupvalue. Once it is done, still are you getting #N/A error?

Then you need to use the Ctrl + F option and give the lookup value related word to check whether the lookup value related word is available or not. If there are no such values then you vlookup is providing you correct error.

Sometimes due to unwanted spaces in the lookup value text, this error can be thrown, at that time you need remove the unwanted spaces from the lookup value related column in the data.

This error is known as logical error because we have write the correct formula still we did not get the result lack of look value in the data.

Troubleshooting VLOOKUP #N/A error is we need to either correct the lookup value related word like remove the trailing or leading spaces from the word. If that particular word is unavailable we can use the iferror with vlookup or isna with vlookup to bypass the error from the formula results

Example:

If you see in the below example lookup value Rajesh is unavailable in the E column but E column related value is the Lookup value. Hence we got this #N/A error in the vlookup functions

Vlookup #NA Error
Vlookup #NA Error

2)  Vlookup #VALUE Error:

This error occurs when we write the syntactical mistake in the vlookup formula . Hence when writing the vlookup, we need to ensure that are writing correct syntax else it throws the #VALUE error in the result. This can be occurred very rare scenarios because we all know that vlookup is very simple yet powerful formula. Once we get habituated with this formula, we never ever write the incorrect syntax for vlookup. Only when we are in the initial stage, that time there is a chance to write this kind of mistakes

 Fixing #VALUE error in VLOOKUP formulas:

We need to check where exactly missed our syntax and correct it. Then it works fine.  In order to do that first lookup for lookup value if that is given then go for the table array whether we have given that correctly or not, if that is also correct then check whether you have given the column index number or missed. And last finally you check whether you mentioned the exact match or approximate match. If you check these all parameters it should not have any syntactical error then obviously this error will not be appeared in your result

Example:

If you see in formula bar, you can find I have missed out the Lookkup value at first block. Hence we got this error #VALUE, if we mention correctly then result can be  populated.

Vlookup #VALUE Error

 

1)  Vlookup #NAME Error:

When you are writing the vlookup function and if you miss any letter from the vlooup spelling and write the formula then obviously you get this #NAME error.

Hence Vlookup #NAME Error can be fixed by just checking he spelling of the vlookup function.

 

Example:

if you see the formula bar in the below picture, I have written the unwanted space in the vlookup formula like vlook up. That is why we got this #Name error in the below scenario.

Vlooup #NAME Error
Vlooup #NAME Error

 

These all errors are common error in the vlookup function and you can bypass the #N/A error by writing the IfError function. If you write the vlookup function within the IFERROR function, you can catch the vlookup error and bypass the error message

How to fix the Vlookup errors:

  1. Vlookup #N/A Error can be bypassed by iferror function and provide the lookup value which available exactly in the lookup value column.
  2. Vlookup #VALUE error would be fixed by checking the syntax of the vlooup and correct that, then we can see the exact result if there is the correct lookup value
  3. Vlookup #NAME error can be rectified by checking the spelling the vlookup function and correct parenthesis in the formula.

 

 

 

77 COMMENTS

  1. Your blog is helping our company to grow in market. Because of your excel formulas related articles, our executives are easily learning new tricks in excel formulas. Thank you very much

  2. We like to honor your blog. That’s why i would like share your blog to all my students. Thanks it is really worth and easy to teach excel formulas using your website

  3. I think you are an expert in excel to write like this kind effective articles to teach excel to visitors. It is really a good move.

  4. Very few web sites are there like your blog. Very informative about excel formulas. Thank you and keep writing. It will become very soon.

  5. Every 60 minutes there are new posts are updates from your blog. We are very eager to read your articles. Please provide more articles to read and learn more quickly. Thank for providing Vlookup Errors topic

  6. Every 60 minutes there are new posts are getting added to your blog. How are managing to write these many posts daily. Thank you for your help

  7. I am more than happy to find this site. I need to to thank you for ones time just for this fantastic read!!
    I definitely really liked every bit of it and i also have you bookmarked to see
    new information on your website.

  8. This is very interesting, You are a very skilled blogger.
    I have joined your rss feed and look forward to seeking more of your wonderful post.
    Also, I have shared your website in my social networks!

  9. Usually I do not learn article on blogs, but I
    wish to say that this write-up very forced me to try and do so!
    Your writing style has been amazed me. Thanks, very
    great post.

  10. I read this post fully regarding the comparison of hottest and preceding technologies, it’s remarkable article.

  11. It’s amazing to pay a quick visit this website and reading the views
    of all mates regarding this paragraph, while
    I am also keen of getting know-how.

  12. Nice weblog right here! Additionally your site loads up
    very fast! What web host are you the use of? Can I am getting your affiliate hyperlink on your host?

LEAVE A REPLY

Please enter your comment!
Please enter your name here