There are certain situations when we encounter error on our vlookup function, such as if we use approximate match but the data cannot be matched in the column or table we should switch to exact match to match the data, also there are situations when table or data is not sorted we need to sort the data, if the reference value is on the right we need to use Index and match functions.
VLOOKUP Errors (Table of Contents)
- #1 – Fixing #N/A Error in VLOOKUP
- #2 – Fixing #VALUE! Error in VLOOKUP
- #3 – Fixing VLOOKUP #REF Error
- #4 – Fixing VLOOKUP #NAME Error
Top 4 Errors in VLOOKUP and How to Fix Them?
If you know VLOOKUP formula, it is a good function to have in your kitty. However, if you think knowing the VLOOKUP is the ultimate thing then your assumption is wrong.
The way VLOOKUP give you the result it also gives you some kind of errors in excel. I would say knowing the VLOOKUP is not the ultimate thing rather finding those errors and fixing those errors is the ultimate things in excel.
VLOOKUP simply cannot gives you errors because of data mismatch it will return the errors.
- #N/A Error
- #NAME? Error
- #REF! Error
- #VALUE! Error
Here we will discuss the four kinds of errors #N/A, #VALUE! #NAME? & REF!.
#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 the 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 below steps to rectify the error.
Step 1: Apply LEN formula and find how many characters are there in the cell A4 & E4.
In the cell C4, I have applied 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 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 VLOOKUP #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 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
- VLOOKUP #N/A error due to data mismatch.
- VLOOKUP #NAME error due to wrong formula type.
- VLOOKUP #REF error is due to a wrong column index number.
- VLOOKUP #VALUE! The error is due to missing or wrong parameter supply.
You can download this Fix Errors in VLOOKUP Excel template here – Fixing VLOOKUP Errors Excel Template
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
- P-Value Excel
- Excel HLOOKUP Examples
- VLOOKUP with SUM Function in Excel
- VLookup with IF Function
- Number in Excel
- Alternatives to Vlookup
- VLOOKUP with Many Criteria
- IFERROR with VLOOKUP
- VLOOKUP vs HLOOKUP Differences
- How to COUNTIF with Multiple Criteria
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
