Top 4 Errors in VLOOKUP and How to Fix Them?
VLOOKUP simply cannot gives you errors because of data mismatch; it will return the errors.
- #N/A Error
- #NAME? Error
- #REF! Error
- #VALUE! Error
Let us discuss each of the error in detail with an example –
#1 Fixing #N/A Error in VLOOKUP
This error usually comes due to any one of the many reasons. #N/A means simply Not Available is the result of the VLOOKUP formula if the formula is not able to find the required value.
Before head into fixing this problem, we need to know why it is giving error as #N/A. This error is due to data entry mistake, due to approximate match criteria, due to wrong table references, wrong column reference number, data not in vertical form, etc.
I have a simple sales report table in Table 1. In Table 2, I have applied the VLOOKUP formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more and trying to extract the values from Table 1.
In the cell F4 and F9, I got errors as #N/A. The value in the cell E4 looks as exactly as the value in the cell A4, but still, I got an error a #N/A. Now you must be thinking why VLOOKUP has returned the result as #N/A. Nothing to worry; follow the below steps to rectify the error.
- Step 1: Apply the LEN excelLEN ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more formula and find how many characters are there in the cell A4 & E4.
In cell C4, I have applied the LEN function to check how many characters are there in the cell A4, and similarly, I have applied the LEN function in the cell D4 to find how many characters there in the cell E4.
In A4 cell, I have 11 characters, but in cell E4, I have 12 characters. One extra character is there in the cell E4 when we compare to the cell A4.
By looking at the outset, both look similar to each other. However, one extra character is there, and it must be a trailing space.
We can just edit the cell E4 and delete the space. If we delete this extra space, we will get the result.
But that is not the correct way to solve the issue.
- Step – 2: We can remove trailing spaces by using the TRIM function in excelTRIM Function In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces.read more. By applying VLOOKUP along with the TRIM function, we can automatically delete the spaces.
The TRIM function removes the extra unwanted space.
#2 Fixing #VALUE! Error in VLOOKUP
This error is due to missing any one of the parameters in the function. Look at the below table, for example.
VLOOKUP starts with LOOKUP value, then table range, followed by a column index number and match type. If you look at the above image, formula parameters are not in perfect order. In the place of lookup value table range is there, in the table range place we have column index number and so on.
We can simply need to mention the formula correctly to remove this error.
#3 Fixing VLOOKUP #REF Error
This error is due to the wrong reference number. When we are applying or mentioning the column index number, we need to mention the exact column number from which column we are looking at the required result. If we mention the column index number that is out of the selection range, then this will return #REF! error.
The lookup value is perfect; the table range is perfect, but the column index number is not perfect here. I have selected the table range from A3 to B8, i.e., only table range from A3 to B8, i.e., only two columns I have selected.
In the column index number, I have mentioned 3, which is out of range of the table range, so VLOOKUP returns the #REF! error result.
Mention the correct column index number to rectify this error.
#4 Fixing VLOOKUP #NAME Error
We get this VLOOKUP #NAME Error due to the wrong formula mention. In my personal experience, I usually type CLOOKUP instead of VLOOKUP.
There is no formula called Clookup in excel so returned the values as #NAME? error type.
Solution: The solution is straightforward; we just need to check the spelling of the formula.
Things to Remember Here
- #N/A error due to data mismatch.
- #NAME error due to the wrong formula type.
- #REF error is due to a wrong column index number.
- #VALUE! The error is due to missing or wrong parameter supply.
Recommended Articles
This has been a guide to VLOOKUP Errors in Excel. Here we discuss how to fix the four most common errors #N/A, #VALUE! #NAME? & REF! in VLOOKUP along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers