In both Microsoft Excel and Google Sheets, Vlookup is a widely used and popular function. It is disheartening when users encounter Vlookup not working errors while using the function.
The majority of users report that they encounter issues like Microsoft Excel Vlookup not working, Vlookup returning n/a, Vlookup not finding value, and several other Vlookup errors.
These Vlookup problems are usually because of certain limitations that are part and parcel of the functionality but they are also caused by users failing to adhere to rules and syntax.
Whether the Vlookup not working error is caused by a manufacturing limitation or by the actions of the end-user, this article will help you to fix Vlookup not working error and other related Vlookup issues.
I will be showing you some common errors and Vlookup troubleshoot guidelines to resolve such errors.
This post has covered to a considerable extent the common Vlookup problems and their worthy solutions.
Why Is Vlookup Not Working?
In this section, I would try and address the problems you will likely face as you make use of Vlookup.
We would take it one error after the other. Take your time to read through all of them to detect what is causing the error in your case.
Vlookup #na Error
For some reason, you can encounter the Vlookup #na error when a value exists which is an irony.
The meaning of this error is that the lookup value is not available (#na). It is not always correct that the lookup value is not available.
Regardless, there could be some reasons for this error and I have tried to explain these reasons and their solutions.
Causes and How To Fix Vlookup #na Error When Value Exists
Wrongly Entered Values in Lookup_value
When you enter the wrong value in the lookup_value argument, Vlookup returns the #na error.
Check and ensure that your lookup value is entered correctly.
Spaces in Lookup Value
Vlookup returning n/a can be because there are extra spaces in the lookup value.
To fix the Vlookup returning n/a error, you will need to use the TRIM function and wrap the Lookup_value argument in the Vlookup formula.
To do this, use the function below:
Formatting Numeric Value as Text
If you format the numeric values in a table_array argument as text in the Vlookup function, you will encounter the Vlookup returning n/a error.
The quick fix for this is to check if you have properly formatted the numeric values. Numeric values should be formatted as “Number” not “Text”.
Lookup value Missing From First Column of Table Array
It is a rule for the lookup value to be in the first column of a table_array argument. If the lookup value is not in the first column, Vlookup will return the #na error.
To fix the error, you will need to rearrange the columns correctly, selecting the table_array in the Vlookup function.
Vlookup #Value Error
Your Microsoft Excel Vlookup not working could be because of the #value error.
Entering the wrong data type in the formula of your Microsoft Excel will generate the #value error.
In the case of Vlookup, three possible circumstances would lead to such an error.
Causes and How To Fix Vlookup #Value Error
Index_number < 1
Any index_number that is less than 1 added to the index_number argument will return the Vlookup #value error.
If Vlookup returns the #value error, check if the index_number argument has a value that is less than 1.
Incorrect or Incomplete Workbook Path
Supplying the table_array from another workbook may return the #value error if the path is broken or incorrect.
So if you are supplying the table_array argument from another workbook and the #value error occurs, you will need to follow the syntax below to provide it:
=VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE)
Should something be missing, the Vlookup formula will return the #value error except the lookup workbook is open.
Lookup Value Exceeds Character Length
The maximum number of characters allowed in the Vlookup lookup_value argument is 255. Going beyond the stipulated 255 character length will make the formula return the #value error.
There are two ways to fix the #value error. The first way is to reduce the characters in the lookup value, ensuring it falls within 255 characters.
Alternatively, instead of using the Vlookup function, you can use the INDEX, MATCH formula.
The following syntax shows how:
=INDEX (returing_range,MATCH(TRUE,INDEX(lookup_range = lookup_value,0),0))
Vlookup #Ref Error
This Vlookup error is caused when the index_number argument is greater than the number of columns in table_array.
To fix the Vlookup #ref error, check the index_number argument in the Vlookup function and make sure it is not greater than the table_array columns.
Vlookup Returning Incorrect Result
The last Vlookup not working error we will be looking at is when the function returns incorrect values.
This error occurs consequently upon omitting the supply match type in the range_lookup argument of Vlookup.
When this omission takes place, Vlookup searches for match values and since it is unable to find any it returns incorrect results.
Vlookup will also return incorrect results if the table_array argument is sorted in descending order instead of the default ascending order by the first column.
How To Fix Vlookup Returning Incorrect Result
Supply a relevant match type in the range_lookup argument as “True” or “False”.
In the approximate match type that is “True”, sort the table_array argument in ascending order by the first column.
I believe you will be able to fix Vlookup not working error and other Vlookup problems when they arise from now on.
If you encounter any challenges while trying to fix Vlookup not finding value error or any Vlookup issues, feel free to contact me.
It would be a joy to help you fix them. Also, remember that you need to sure what the error is before applying a solution.
When you understand what the error is and its cause which I have already shared in the body of this article, then you can apply the preceding solution.