VLOOKUP Errors in Excel
MS Excel, short for Microsoft Excel, is a powerful spreadsheet program that enables users to record data and perform various operations using functions and formulas within multiple cells. VLOOKUP is one of the existing Excel functions. This allows us to search for and return data from any other column in our entire workbook. However, the function can seem tricky if we're not familiar with it, especially when using it directly in a cell. If we apply the VLOOKUP function incorrectly, it gives various errors or may give wrong results.
This article discusses the various VLOOKUP errors that we commonly encounter while working in Excel. Due to VLOOKUP errors, we get either incorrect results or specific error codes that indicate that something is wrong with the formula applied.
What are the common VLOOKUP Errors in Excel?
In Excel, VLOOKUP is a widely used function that helps extract specific values from various data sources. However, the function also has several limitations. When we do not follow certain limitations or specifications of VLOOKUP, it usually leads to unexpected results and errors. This is why many Excel experts consider VLOOKUP to be one of the most complex Excel functions.
The following are the most common errors that often appear while using the VLOOKUP function in Excel:
To avoid VLOOKUP errors, we must ensure to use properly formatted data without the blank cells, incorrectly formatted values, and deleted formulas. Also, we can use the IFERROR function with VLOOKUP. It helps to ignore the error and return whatever is placed in the place of the error.
Let us now discuss each VLOOKUP error in detail and know the ways to fix it accordingly:
#N/A Error in VLOOKUP
The #N/A error in VLOOKUP usually occurs when the formula does not find the desired lookup value to produce results. The term N/A means 'Not Available'. This error can occur due to one of the following reasons:
1. The lookup value is misspelled
We may type a lookup value with some spelling mistakes when dealing with a large data set. In that case, the VLOOKUP formula returns the #N/A error because it does not find the mistyped value in the defined table range.
In the above sheet, the formula searches for a value 'Speaker' while the table range has a value entered as 'Speakers'. The formula does not find the exact match as one 's' is missing in the lookup value. Correcting the typo in the lookup value fixes the #N/A error.
2. #N/A error in exact match VLOOKUP
When we apply the VLOOKUP to search for an exact match (range_lookup argument is FALSE), the formula returns the #N/A error if no exact match is found in the defined table range.
In the above sheet, the formula is searching for an ID with the number 100 (in cell D4) returns an #N/A error because the minimum ID number in the range is 101. The formula does not find the exact match. Giving the proper matched lookup value fixes the #N/A error.
3. #N/A error in approximate match VLOOKUP
When we apply the VLOOKUP to search for the closest or approximate match (range_lookup argument is TRUE), the formula returns the #N/A error in the following two cases:
4. The lookup column is not the left-most column
One of the most common reasons for the #N/A error in VLOOKUP is that it is given a table array or a range that does not contain the lookup column to the left. The LOOKUP formula cannot return a value from its left. Therefore, a lookup column must always be structured to the left-most side in the table array. We usually forget this limitation of the VLOOKUP formula and get the respective error.
Moving the lookup column to the left-most side in the defined table array fixes the #N/A error. After moving the column to the left-most side, we must specify the array table or a range again.
If it is not possible to switch the columns or reorganize data to make the lookup column the left-most column due to some reasons, we must avoid using the VLOOKUP formula. Instead, we can use INDEX and MATCH functions together as an alternative to the VLOOKUP.
5. Numerical values formatted as text
The formatting of cells also plays an important role in VLOOKUP. If our data set contains numbers formatted as text, either in the main or lookup table, the VLOOKUP formula will usually return a #N/A error. This usually happens when we copy or import data from other sources instead of entering it manually. Also, the numbers starting with an apostrophe are interpreted as text by Excel.
If our sheet has text-formatted numbers, the error indicator is displayed for such cells. If we select such cell(s), Excel also displays a message accordingly.
Converting the text-formatted numbers to numbers usually fixes the #N/A error. To convert numbers that are stored as text into exact numbers, we must first select all such problematic cells/numbers. After that, we need to click the error icon and select the 'Convert to Number' option from the list.
6. Leading or trailing spaces
Another common reason behind the #N/A error in VLOOKUP is the presence of one or more extra spaces, either in the beginning or the ending of the cell value. There can be the following two cases when dealing with spaces in VLOOKUP:
#NAME? Error in VLOOKUP
#NAME? error is one of the most common and easy to fix VLOOKUP error. This error typically appears when we accidentally enter the wrong or misspelled function name. For example, we may type VLOKUP or CLOOKUP in place of the VLOOKUP and see the #NAME? error instead of the expected results.
In the case of the misspelled function name, Excel does not find the function in the library and returns the values as #NAME? error type.
To fix the #NAME? error in VLOOKUP, we must ensure to enter the correct function or formula name. After the function name is corrected, the error is resolved.
#REF! Error in VLOOKUP
The #REF! error in VLOOKUP appears because of the wrong reference number. When we enter the VLOOKUP formula, we must specify the exact column index number; a column used to obtain a result. If we accidently type the column index number higher than the selected range, the VLOOKUP formula will return the #REF! error.
We have supplied the correct table range and the lookup value in the above sheet, but the entered column index number (which is 3) is out of the selection range. Since we have selected the range A2:B9, only two columns are supplied within the table range. Correcting the column index number fixes the #REF! error in VLOOKUP.
Since we have only two columns and column second is the main column we need to extract the resultant value from, we specify the column index number as 2, not 3.
#VALUE! Error in VLOOKUP
Generally, the #VALUE! error in VLOOKUP occurs when a value supplied in the respective formula is of a wrong data type. In addition to this, there are some other reasons as below:
1. Lookup value contains more than 255 characters
Unfortunately, Excel's VLOOKUP formula cannot look up values exceeding 255 characters. If there are more than 255 characters in the Lookup value, the formula returns the #VALUE! error.
To fix #VALUE! errors in such cases, we must use a combination of INDEX, MATCH functions in the following way:
After entering the entire INDEX and MATCH combination formula, we must ensure to press 'Ctrl + Shift + Enter' instead of only the Enter key.
2. Missing Parameters or Arguments
If the VLOOKUP formula is applied with any missing argument, it can lead to #VALUE! error. The VLOOKUP formula must be supplied with the LOOKUP value, then the table range, followed by a column index number and match type.
In the following sheet, the #VALUE! error appears due to a missing LOOKUP value in the applied formula.
To fix the #VALUE! error in VLOOKUP, we must supply all the necessary parameters and ensure they are in proper order.
3. The col_index_num is less than 1
If we accidently type the column index number less than 1, the VLOOKUP formula will return the #VALUE! error.
In the above sheet, we have supplied the correct table range and the lookup value, but the entered column index number (which is 0) is less than 1. It is unusual that a user intentionally types a column number below 1 because we use VLOOKUP to obtain results from a specific column. That means there must be at least a column within the sheet. However, there may be cases when a number is less than 1, especially when this argument is returned by some other function nested in our VLOOKUP formula.
Correcting the column index number fixes the #VALUE! error in VLOOKUP.
Avoiding the VLOOKUP Errors
Excel's VLOOKUP function has several limitations, more than any other existing function in Excel. Because of such limitations, we must always use this function from the Insert Function Wizard. Using the wizard, we get information about the desired arguments, which helps to eliminate errors. In addition, we can follow the precautions below to avoid or prevent errors in our sheets with VLOOKUP: