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
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
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
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.
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.
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.
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:
- Vlookup #N/A Error can be bypassed by iferror function and provide the lookup value which available exactly in the lookup value column.
- 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
- Vlookup #NAME error can be rectified by checking the spelling the vlookup function and correct parenthesis in the formula.