VLOOKUP is very common, popular and widely used function in Excel and Google Sheets. But the majority of users complain that VLOOKUP is not working correctly or not giving incorrect results. It is because of some limitations with VLOOKUP function and sometimes users also do not carefully follow its rules and syntax while using this function. Here, we are going to discuss some of the common errors and reasons why VLOOKUP does not work.
In this article, we are going to discuss VLOOKUP errors, like #NA, #VALUE, #REF, and VLOOKUP returning incorrect results. Now you are going to see the reasons for these errors and their solutions.
VLOOKUP #NA error
When VLOOKUP formula cannot find a match then this error displays, meaning “not available”. But this is always not correct that lookup value is actually not available. There could be some reasons due to which VLOOKUP returns this error.
- Extra Spaces in Lookup ValueThis is one of the most common reasons behind #NA error in VLOOKUP. In big data set it is very hard to identify these leading or trailing spaces in lookup values that cause VLOOKUP function to not found the match and return #NA error.
To kill these extra spaces you need to wrap the Lookup_value argument in VLOOKUP formula with TRIM function to ensure correct working of the function, such as;
- Typo mistake in Lookup_ValueIf you wrongly enter the value in lookup_value argument of VLOOKUP function then it generates #NA error. So you must enter the lookup value correctly in lookup_value argument.
- Numeric values are formatted as TextIf numeric values are formatted as text in table_array argument of VLOOKUP function then it comes up with #NA error.
To fix this error you must check and properly format the numeric values as “Number”
- Lookup Value not in First column of table arrayAs per rule lookup value must be in first (leftmost) column of table_array argument of VLOOKUP function. If lookup value is not present in first column of table_array then VLOOKUP generates #NA error.
To fix this error, you must arrange your columns correctly and then select your table_array in VLOOKUP function.
- In case of Approximate Match typeIn case of approximate match type (TRUE), your VLOOKUP function generates #NA error if your lookup value is smaller than the smallest value available in first column of table_array.
VLOOKUP #VALUE error
Generally, if you enter wrong data type in the formula in Excel then formula generates #Value error. But in case of VLOOKUP function, there are following three reasons that should look into.
- Index_number less than 1If you enter index_number argument less than 1 in VLOOKUP function then it returns #VALUE error. So you must check index_numberargument if VLOOKUP argument returns this error.
- Workbook path is incorrect or incomplete: When you supply the table_array from another workbook in VLOOKUP and path of that workbook is incomplete then VLOOKUP returns #VALUE error. So you need to follow its following syntax to provide it fully.
=VLOOKUP(lookup_value, '[workbook name]sheet name'!table_array, col_index_num, FALSE)
If anything in the path format is missing, VLOOKUP formula returns #VALUE error, unless the lookup workbook is currently open.
- Lookup value characters lengthVLOOKUP supports a maximum of 255 characters length of a lookup value argument. If lookup value character length exceeds this limit in VLOOKUP then formula returns #VALUE error.
Either you reduce the character length of lookup value to the maximum limit of 255 characters in VLOOKUP function or you should use INDEX, MATCH formula instead of VLOOKUP function in the following pattern;
=INDEX (returing_range,MATCH(TRUE,INDEX(lookup_range = lookup_value,0),0))
VLOOKUP #REF error
If index_number argument of VLOOKUP is greater than the number of columns in table_array, then VLOOKUP function returns #REF error. So you need to check and rectify the index_number supplied in function.
VLOOKUP returning incorrect results
If you omit to supply match type in range_lookup argument of VLOOKUP then by default it searches for approximate match values, if it does not find exact match value. And if table_array is not sorted in ascending order by the first column then VLOOKUP returns incorrect results.
You must always supply relevant match type in range_lookup argument of VLOOKUP as TRUE or FALSE. And in case of approximate match type (TRUE), you must always sort your table_array in ascending order by the first column of your table_array.