No matter whether you are a newbie or a pro-Vlookup user, you will certainly come across some errors at some point in time in your Vlookup use. What are those common errors in Vlookup in Google Sheets?
For explanation purposes, I am putting the most common Vlookup errors under two categories.
Vlookup errors related to syntax (this will cause error value outputs that start with #)
Accidental Vlookup mistakes (this will cause wrong/unexpected results)
There are 4 main Vlookup errors associated with wrong syntax use in Google Sheets. They are:
There may be several reasons for these common Vlookup errors in Google Sheets. I will try to explain the main reasons one by one and how to address them.
Other than these, it’s important to address the cause of wrong results in the Vlookup output due to the accidental mistakes that we make. So here we go!
Common Vlookup Errors Associated with Syntax in Google Sheets
#N/A! Error in Vlookup in Google Sheets
Tooltip: Did not find value ‘Student 6’ in VLOOKUP evaluation.
If you ask me, which is the most commonly occurring error in Vlookup, without any doubt, I would say the N/A error.
To understand the cause of this error, let me give you one example.
Here is the Vlookup Syntax:
VLOOKUP(search_key, range, index, [is_sorted])Here is the #N/A error I am talking about.
=vlookup(E2,B1:C5,2,0)
When the search_key (here “Student 6”) is not available in the Vlookup ‘range’ (in the first column of the range), the formula would return an #N/A! error.
Solution (Vlookup without #N/A in Google Sheets)
To hide/remove the Vlookup #N/A! error, only use the IFNA function. Here is how and why?
To replace Vlookup #N/A! error with blank in Google Sheets, place Vlookup within IFNA().
=ifna(vlookup(E2,B1:C5,2,0))
How to return 0 instead of #N/A in a Google Sheets Vlookup formula?
Here is the answer!
=ifna(vlookup(E2,B1:C5,2,0),0)
To replace Vlookup #N/A! error with a custom message in Google Sheets, use this one.
=ifna(vlookup(E2,B1:C5,2,0),"your custom message")
You may happen to see the use of IFERROR instead of IFNA to remove the #NA! error. I have also used that many times.
The reason for that is IFNA in Excel as well as in Google Sheets is a relatively new function.
What Is the Problem with IFERROR Then?
As I have mentioned, the #N/A! is one of the commonly occurring errors in Vlookup in Google Sheets. Actually, it’s not a proper error!
It means the search value is Not Available in the first column of the Vlookup formula ‘range’.
If you use to remove the said common error with IFERROR, you are harming your chance to rectify any other unforeseen Vlookup syntax errors.
#VALUE! Error – The Second Most Common Error Type in Vlookup in Google Sheets
The main reason for the #VALUE! error in Vlookup is the wrong use of the arguments. You must understand the reasons to correct the #VALUE! error in Vlookup. Let’s start one by one.
#VALUE! Error 1:
Tooltip: An array value could not be found.
=vlookup(B1:C5,E2,2,0) or the alternative Vlookup =vlookup(B1:C5,"Student 3",2,0)
The error is associated with the improper use of the syntax. As per the syntax, the search_key should come first in the order.
Correct Vlookup Formula:
=vlookup(E2,B1:C5,2,0)
#VALUE! Error 2:
Tooltip: Function VLOOKUP parameter 3 expects number values. But ‘Points’ is a text and cannot be coerced to a number.
=vlookup(E2,B1:C5,"Points",0)
Do not use field labels as a string instead of the Index number. It will cause the above error.
#VALUE! Error 3:
Function VLOOKUP parameter 3 value is 0. It should be greater than or equal to 1.
This error is happening due to a typo. The index number should start at 1. If you accidentally use 0, the above error will appear.
=vlookup(E2,B1:C5,0,0)
#REF! Error in Vlookup in Google Sheets
If you delete the range referred to in Vlookup, the formula would return the #REF! error.
Tooltip: Reference does not exist.
=VLOOKUP(C2,#REF!,2,0)
Another reason for the #REF! is the use of the non-existing range.
Assume my Sheet has only 10 rows. Then what about using a non-existing range B15:C in Vlookup as below?
=VLOOKUP(E2,B15:C,2,0)
The formula will cause #REF! error.
Tooltip: G20011151!B15:C evaluates to an out-of-bounds range.
The number in the first part of the error message will be the GID of your Sheet.
#NAME? Error in Vlookup in Google Sheets
It’s another common error in Vlookup in Google Sheets. It can be caused by typos and other issues.
#NAME? Error Due to the Use of Field Label as Index
Tooltip: Unknown range name: ‘POINTS’.
=vlookup(E2,B1:C5,Points,0)
Database functions (example DGET) support field labels instead of the column index (the column order in the range) in formulas.
If you accidentally use field labels in Vlookup, the formula would return the #NAME? error.
#NAME? Error Due to the Typo in Named Ranges
I am very rarely using the NAMED RANGES (Data menu) in Vlookup. But there are many takers out there who use it to simplify the formulas.
Assume I have named the range B1:C5 as “Class” so that I can use it in Vlookup as the ‘range’.
=vlookup(E2,Class,2,0)
I have made a typo below.
=vlookup(E2,Class,2,0)
The formula will return the #NAME? error and the same tooltip above. To avoid committing this error, Google has its own way.
When you start typing a named range in Vlookup or anywhere within the Sheet, Google will suggest the correct named range. Pick from the list to auto-complete the named range name.
Error in Function Name
The wrong function name, for example, “vlokup” or “vloookup”, can cause #NAME? error.
Tooltip: Unknown function: ‘vlokup’.
=vlokup(E2,Class,2,0)
Common/Accidental Vlookup Mistakes and Wrong Results
Even if you correctly use the Vlookup formula, sometimes you will not get the correct result. Here are three scenarios and tips to fix them.
#N/A! Due to Extra Space in Vlookup – Fix It
My Vlookup formula is correct. But still, it returns #N/A!? Why?
Extra space can cause ‘match’ related issues in almost all formulas. Vlookup is no exception.
In this example, the Vlookup returns #N/A! due to the extra space on the search_key side. Just remove that whitespace.
=VLOOKUP(E2,B1:C5,2,0)
Sometimes the search_key will be correct. The extra space will be on the range (first column).
How to Fix Whitespace (Extra Space) Issue in Vlookup Range?
There are two solutions to remove the extra space issue in the Vlookup range in Google Sheets. What are they?
- Select the first column in the range (here B1:B5). Then go to the Data menu and select Trim whitespace. This will solve the problem.
- I may prefer the below formula instead which trims whitespace.
=ArrayFormula(VLOOKUP(E2,trim(B1:C5),2,0))
Fix Formatting Error in Vlookup in Google Sheets
The Vlookup search_key should be formatted (number/date/text) as per the first column in the range.
If the date is DD/MM/YY format in your range, the search key should also be in the same format. I mean DD/MM/YY or DD/MM/YYY but never MM/DD/YY.
If a number is the search_key and even if the same number is available in the range, Vlookup may rarely return #N/A! error.
In that case, check whether the first column in the range is formatted as text. If it is text formatted, select that column and go to the Format menu, Number, and select Number.
Still having the problem? Look for extra space (whitespace) as explained above.
Vlookup Returns Wrong Result Other Than Error
The only reason for the wrong result in Vlookup is the improper use of the is_sorted argument.
You must use 0, not 1, in most cases. Use 1 if you are sure that the range (the first column) is sorted.
Otherwise, use the SORT function within the Vlookup range.
=vlookup(E2,sort(B2:C5,1,1),2,1)
I hope I have covered almost all the common errors in Vlookup in Google Sheets.
For more useful Google Sheets tips and tricks, visit Crawlan.com.
