Excel VLOOKUP Error
At the initial stage of learning formulas, everything seems to in place but later on, you may encounter different scenarios and which result in throwing up the error values from the formulas applied. When it comes to errors VLOOKUP is not a strange formula because errors are always associated with formulas.
Types of Errors in VLOOKUP Excel Function
With VLOOKUP, in general, we can see 4 types of errors. Below is the list –
So we will see one by one why those errors occur because of different reasons. One thing we need to remember here is VLOOKUP is programmed properly but because of user’s mistakes, we will get to see those errors. Anyway, we will see those errors explanation in detail now.
Type #1 – #N/A Error in VLOOKUP Function
#N/A is called as “Not Available” error. This error occurs mainly because when the lookup value is not found in the table array.
Not only because lookup value is found in the table array but there are also chances where extra spaces may cause this error. For example in table array value is “New York” but if the lookup value is “New York” (extra space is there) then this not available error occurs.
For an example look at the below image.
As you can see above we have “Butter Fruit” in table array and “Butter Fruit” in lookup value cell (D2 cell), so VLOOKUP has returned error, this is because in the lookup value cell (D2 cell) after the word “Butter” there are two space characters, so lookup value is not exactly same as in the table array, so not available error comes.
To deal with these cases we can use TRIM function inside the VLOOKUP function to eliminate unwanted space characters of the lookup value.
We have used the TRIM function to select the lookup value D2 cell, so TRIM will remove all the unwanted space characters and returns the exact value as “Butter Fruit”.
Now, look at the below case of not available error.
This is a genuine case of VLOOKUP returning a not available error. Lookup value (D2 cell) “Kiwi” is not at all available in the table array so #N/A error comes up.
To deal with this error we need to add “Kiwi” fruit name and its price to the table array and another way of dealing with this error is instead of showing ugly error #N/A we can show a friendly message as “Lookup Value is Wrong”. This can be done by the combination of VLOOKUP and IFERROR functions.
Type #2 – #VALUE! Error in VLOOKUP Function
This error occurs when we miss out on any of the arguments of the VLOOKUP function. VLOOKUP consists of 4 arguments and the first three are mandatory arguments, so if you miss out on any of the first three arguments we will get this #VALUE! Error.
Look at the below image.
In the above formula column index number was missed out so #VALUE! error occurs.
The only way we can fix this error is by supplying all the arguments correctly.
Type #3 – #REF! Error in VLOOKUP Function
This is the error of reference when the column index number is out of range of selected table array then it will show this error. For an example look at the below image.
In the above formula, we have selected the table array of two-column but for column index number we have provided is 3, so since there is only two column reference and we are seeking the result from the 3rd column which causes a reference error.
To fix this error we need to provide the correct column number.
4 – #NAME? Error in VLOOKUP Function
This error occurs because of the wrong spelling of the formula. For example, look at the below image of the formula.
As you can see above we have wrongly entered the formula name as VLOOOKUP, so excel could not recognize this function and returns the error.
We need to correctly type the formula to get rid of this error.
Things to Remember
- Each error has its own characteristics, so knowing about the error is very important to fix those errors.
- The #REF! an error may also occur if the formula referenced column deleted after the formula has been applied.
This has been a guide to VLOOKUP Error. Here we discuss how to handle all types of VLOOKUP Error (#N/A, #Name, #REF, #VALUE ) along with practical examples and downloadable excel template. You may learn more about excel from the following articles –