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 formula 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 LEN 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 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 the 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 TRIM function. By applying VLOOKUP along with 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 than 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 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.
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: 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 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 –
- VBA IFERROR in Excel
- Vlookup to Left
- VLookup with IF Function
- VLOOKUP with Many Criteria
- IFERROR with VLOOKUP
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
