The Main Reasons Your VLOOKUP Isn’t Working, and How to Fix Them

VLOOKUP is a common, popular, and widely used function in Excel and Google Sheets. However, many users complain that their VLOOKUP function is not working correctly or giving incorrect results. This is due to certain limitations of the VLOOKUP function, and sometimes users don’t follow its rules and syntax correctly. In this article, we will discuss common errors and reasons why your VLOOKUP isn’t working.

Common VLOOKUP Errors

In this article, we will discuss VLOOKUP errors such as #N/A, #VALUE, #REF, and VLOOKUP returning incorrect results. You will now discover the reasons for these errors and their solutions.

#N/A Error in VLOOKUP

When the VLOOKUP formula fails to find a match, it displays this error, which means “not available.” But it’s not always true that the searched value is actually not available. There could be several reasons why VLOOKUP returns this error.

• Extra spaces in the searched value: This is one of the most common reasons behind the #N/A error in VLOOKUP. In a large dataset, it is very difficult to identify these extra spaces at the beginning or end of the searched value, causing the VLOOKUP function to fail to find a match and return the #N/A error.

To fix this issue, you need to wrap the lookup_value argument in the VLOOKUP formula with the TRIM function to ensure the proper functioning of the function, like this:

=VLOOKUP(TRIM(L2);$I$1:$J$9;2;FALSE)

• Typing error in the searched value: If you incorrectly enter the value in the lookup_value argument of the VLOOKUP function, it generates a #N/A error. So, you need to double-check that the searched value is correctly entered in the lookup_value argument.

• Numeric values formatted as text: If numeric values are formatted as text in the table_array argument of the VLOOKUP function, it results in the #N/A error.

To fix this error, you need to check and properly format the numeric values as “Number.”

• The searched value is not in the first column of the table: According to the rule, the searched value must be in the first column (leftmost) of the table_array argument of the VLOOKUP function. If the searched value is not present in the first column of the table_array argument, then VLOOKUP generates a #N/A error.

To fix this error, you need to properly organize your columns and then select your table_array in the VLOOKUP function.

• In the case of approximate match: In the case of an approximate match (TRUE), your VLOOKUP function generates a #N/A error if your searched value is smaller than the smallest available value in the first column of the table_array.

#VALUE Error in VLOOKUP

Generally, if you enter the wrong data type in the Excel formula, it generates a #VALUE error. But in the case of the VLOOKUP function, there are three main reasons to watch out for.

• Index number less than 1: If you enter an index number less than 1 in the VLOOKUP function, it returns a #VALUE error. So, you need to check the index_number argument if the VLOOKUP function returns this error.

• Incorrect or incomplete workbook path: When you provide the table_array from another workbook in VLOOKUP and the path of that workbook is incomplete, then VLOOKUP returns a #VALUE error. So, you need to follow the following syntax to provide it completely.

=VLOOKUP(lookup_value, '[workbook_name]sheet_name'!table_array, column_number, FALSE)

If something in the path format is missing, the VLOOKUP formula returns a #VALUE error unless the lookup workbook is currently open.

• Character length of the searched value: VLOOKUP supports a maximum character length of 255 for the lookup_value argument. If the character length of the searched value exceeds this limit in VLOOKUP, the formula returns a #VALUE error.

To fix this issue, you can either reduce the character length of the searched value to the maximum limit of 255 characters in the VLOOKUP function, or use the INDEX, MATCH formula instead of the VLOOKUP function following the below pattern:

=INDEX(return_range,MATCH(TRUE,INDEX(search_range=searched_value,0),0))

=INDEX($M$2:$M$8,MATCH(TRUE,INDEX($L$2:$L$8=O2,0),0))

#REF Error in VLOOKUP

If the index_number argument of VLOOKUP is greater than the number of columns in table_array, then the VLOOKUP function returns a #REF error. So, you need to check and correct the provided index_number in the function.

VLOOKUP Returns Incorrect Results

If you omit providing the match type in the range_lookup argument of VLOOKUP, then by default, it looks for approximate match values if it doesn’t find an exact match value. And if the table_array is not sorted in ascending order by the first column, then VLOOKUP returns incorrect results.

Solution:
You should always provide a relevant match type in the range_lookup argument of VLOOKUP, either TRUE or FALSE. And in the case of an approximate match (TRUE), you should always sort your table_array in ascending order by the first column of your table_array.

If you still need help with Excel formatting or have other questions about Excel, connect with a live Excel expert here for individual assistance. Your first session is always free.

Are you still looking for additional help with the VLOOKUP function? Check out our comprehensive compendium of VLOOKUP tutorials here.