Excel VLOOKUP Errors (Table of Contents)
- VLOOKUP Errors
- #1 – Fixing VLOOKUP #N/A Error
- #2 – Fixing #VALUE Error in VLOOKUP formula
- #3- Fixing #NAME Error in VLOOKUP formula
- #4- Fixing VLOOKUP not working (problems, limitations, and solutions)
VLOOKUP Errors
While we use the Vlookup function, there are some instances when we may get an error like #N/A. #Value and #Name which commonly occur in Vlookup. To avoid such errors there are 2 ways. First is, use the properly formatted data where there are no blank cells, values in incorrect format and remove the formulas if there. And another way is by using the IFERROR Function before VLookup. IFERROR function ignores the error returns anything in place of error whatever we place for error.
The common errors when VLOOKUP doesn’t work are:
- VLOOKUP #N/A error
- #VALUE error in VLOOKUP formulas
- VLOOKUP #NAME error
- VLOOKUP not working (problems, limitations, and solutions)
#1 – Fixing VLOOKUP #N/A Error
This #N/A error means Not Available. This error comes with the following reasons:
- Due to misspelled a lookup value argument in the function
We should always first check the most obvious thing. Misprint or type errors occur when you are working with large datasets or when a lookup value is typed directly in the formula.
- The lookup column is not the first column in the table range
One constraint of the VLOOKUP is that it can only look for values on the leftmost column in the table array. If your lookup value is not in the first column of the array, it will display the #N/A error.
Example #1
Let’s take an example to understand this problem.
We have given the product details.
Let’s assume that we want to retrieve the number of units sold for Bitterguard.
Now we will apply the VLOOKUP formula for this as shown below:
And it will return #N/A error in the result.
Because the lookup value “Bitterguard” appears in the second column (Product) of the table_array range A4:C13. In this condition, a formula is looking for the lookup value in Column A, not in Column B.
Solution For VLOOKUP #N/A Error
We can fix this problem by adjusting the VLOOKUP to reference the correct column. If that’s not possible, then try to move the columns so that the lookup column is the leftmost column in the table_array.
#2 – Fixing #VALUE Error in VLOOKUP formula
The VLOOKUP formula displays the #VALUE error if a value used in the formula is of a wrong data type. There can be two reasons for this #VALUE error:
The lookup value should not be more than 255 characters. If it exceeds this limit, this will end up with the #VALUE error.
Solution For VLOOKUP #VALUE Error
By using INDEX/MATCH functions instead of VLOOKUP function, we can overcome this problem.
- The correct path is not passed as the second argument
If you want to pick the records from another workbook, you have to conclude the full path to that file. It will include the workbook’s name (with extension) in square brackets [], and then specify the sheet’s name followed by the exclamation mark. Use apostrophes around all this in case either a workbook or Excel sheet name contains spaces.
The syntax of the complete formula to do a VLOOKUP from another workbook:
=VLOOKUP(lookup_value, ‘[workbook name] sheet name’ !table_array, col_index_num, FALSE)
If anything is missing or any part of the formula is missing, VLOOKUP formula won’t work and it will return the #VALUE error in the result.
#3 – Fixing #NAME Error in VLOOKUP formula
This problem occurs when you have accidentally misspelled the function’s name or argument.
Example #2
Let’s again take the product table details. We need to find out the number of units sold in reference to the product.
As we can see that we have misspelled the spelling of FALSE. We type “fa” in place of false. It will return the #NAME error as a result.
Solution For VLOOKUP #NAME Error
Check the spelling of the formula before hitting enter.
#4 – Fixing VLOOKUP not working (Problems, Limitations, and Solutions)
VLOOKUP formula has more limitation than any other Excel functions. Because of these limitations, it might often return results different from what you expect. In this section, we will discuss for a few common scenarios, when VLOOKUP function fails.
- VLOOKUP is case-insensitive
If your data containing several entries in UPPER and LOWER letter case, then VLOOKUP function works the same for both types of cases.
- A Column has been inserted or removed from the table
If you are reusing the VLOOKUP formula and you made some changes in the dataset. Like inserted new column or deleted any column, it will leave an impact on VLOOKUP function results and it won’t work at that moment.
Whenever you add or delete any column in the dataset, it impacts the arguments table_array and col_index_num.
- While copying the formula, it can lead an error
Always use absolute cell references with the $ sign in table_array. This you can use by pressing the F4 key. That means to lock the table reference so that while copying the formula to another cell, it won’t create a problem.
Things to Remember about VLOOKUP Errors
- In the table, cells with numbers should be formatted as Number, not text.
- If your data contains spaces then it also can lead to an error. Because we can not spot those extra spaces available in the dataset especially when we are working with a large amount of data. Hence you can use the TRIM function by wrapping the Lookup_value argument.
Recommended Articles
This has been a guide to VLOOKUP Errors. Here we discuss how to fix the VLOOKUP errors along with practical examples and downloadable excel template. You can also go through our other suggested articles –
