How do I get rid of Na in VLOOKUP in Google Sheets?

Common Errors in Vlookup in Google Sheets

No matter whether you are a newbie or 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.

1. Vlookup errors related to syntax (this will cause error value outputs that start with #).
2. 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;

1. #REF!
2. #N/A!
3. #VALUE!
4. #NAME?

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, its 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 N/A error.

To understand the cause of this error, let me give you one example. Before that here is the Vlookup Syntax.

Here is the #N/A error I am talking about.

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().

How to return 0 instead of #N/A in a Google Sheets Vlookup formula?

Here is the answer!

To replace Vlookup #N/A! error with a custom message in Google Sheets, use this one.

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 its 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. Lets start one by one.

#VALUE! Error 1:

Tool Tip: An array value could not be found.

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:

#VALUE! Error 2:

Tool Tip: Function VLOOKUP parameter 3 expects number values. But Points is a text and cannot be coerced to a number.

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.

#REF! Error in Vlookup in Google Sheets

If you delete the range referred to in Vlookup, the formula would return the #REF! error.

Tool Tip: Reference does not exist.

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?

The formula will cause #REF! error.

Tool Tip: 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

Its another common error in Vlookup in Google Sheets. It can cause by typos and other issues.

#NAME? Error Due to the Use of Field Label as Index

Tool Tip: Unknown range name: POINTS.

Database functions (example DGET) support field labels instead of 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.

I have made a typo below.

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 withing 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.

Tool Tip: Unknown function: vlokup.

Common/Accidental Vlookup Mistakes and Wrong Results

Even if you correctly use the Vlookup formula, some times you will not get the correct result. Here are such three scenarios and tips to fix it.

#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.

Some times 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 extra space issue in Vlookup range in Google Sheets. What are they?

1. 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.
2. I may prefer the below formula instead which trims whitespace.

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 available in the range, Vlookup may rarely return #N/A! error.

In that case, check whether the first column in the range is formatted to 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 (I think we can say so) reason for the wrong result in Vlookup is the improper use of Is_Sorted argument.

You must use 0 not 1 in most of the cases. Use 1 if you are sure that the range (the first column) is sorted.

Otherwise use SORT function within Vlookup range.

I hope, I have covered almost all the common errors in Vlookup in Google Sheets.

Popular Vlookup Tutorials

1. How to Use Vlookup to Return An Array Result in Google Sheets.
2. How to Use VLOOKUP with Multiple Criteria in Google Sheets [Solved].
3. Dynamic Index Column in Vlookup in Google Sheets.
4. Partial Match in Vlookup in Google Sheets [Text, Numeric and Date].
5. Lookup, Vlookup, and Hlookup Differences in Google Sheets.
6. Reverse Vlookup Examples in Google Sheets [Formula Options].
7. Nested Vlookup in Google Sheets.
8. Create Hyperlink to Vlookup Output Cell in Google Sheets.
9. Vlookup from Bottom to Top in Google Docs Sheets.

• TAGS
• Google Doc
• Spreadsheet
• Vlookup

Video