VLOOKUP Error in Google Sheets (with Quick Solutions)

We use the VLOOKUP function often to lookup for values in our dataset. However, sometimes Google Sheets users get different errors while applying the function. Though these errors mostly happen due to minor mistakes and are very easy to resolve quickly. In this article, we will discuss the VLOOKUP error and how to avoid these errors in Google Sheets. Read along to know more on this topic.

A Sample of Practice Spreadsheet

You can download the practice spreadsheet from the download button below.

5 Most Frequent VLOOKUP Errors in Google Sheets

Suppose, you have a dataset that contains the names of some countries and their capital city names. You will use the VLOOKUP function to find the capital name in the blank search box.

vlookup error google sheets

Now, you can get various errors while using the VLOOKUP function in this dataset. We will show you the errors that can happen while using the function and the steps on how to solve them. So, read along to know the VLOOKUP error in Google Sheets.

1. VLOOKUP #N/A Error

One common error while using the VLOOKUP function is the #N/A Error. This means “ No value is available”. This is a simple error and it can be resolved by taking an easy step.

  • First, select cell C12 and insert the below formula in the formula bar.

vlookup #N/A error google sheets

  • Now, the syntax of the VLOOKUP function is:

VLOOKUP(search_key, range, index, [is_sorted])

The search key should be included in the table. If you insert any data/value that is not already in the table, then it will show a #N/A Error.


You can insert the proper search key from the data table to avoid this Error. You can also use the IFNA function with the VLOOKUP function to avoid seeing the error.

  • First, insert the below formula instead of the previous formula.

  • This formula will replace the error with a blank.
  • If you want to show any text or number in the blank, you can type like the below-mentioned formula.
=IFNA(VLOOKUP(C11,B5:C9,2,0),"No Data")

Read More: Alternative to Use VLOOKUP Function in Google Sheets

2. #REF! Error

Another common error in applying the VLOOKUP function in Google Sheets is #REF! Error. It happens when you don’t apply the correct reference range in the formula. Follow the steps to know the error and its solution.

  • First, apply the below formula in cell C12.
  • After that, you will see #REF! Error in cell C12 because here in the formula, you are looking for the capital city name of a country, which is in the D column, but your reference range has no D column in it. This is why the error is showing.

vlookup #REF! error google sheets


  • Now, if you want to solve this error then put the below formula in the cell.

  • At this time, you can see the formula range is B5:D9. So, it contains the proper reference range. That is why there is no #REF!

Similar Readings

3. #VALUE! Error

You can also get  #VALUE! Error while utilizing the VLOOKUP function in Google Sheets. This happens when you put the arguments in the wrong order.

  • First, insert the following formula in cell C12.

vlookup #VALUE! error google sheets

  • And, you will get a #VALUE! Error for the wrong use of syntax. Here, in this formula, the range is in the first place and then the search key, which is in the wrong order. This is the reason for the error.


Just write the formula following the correct syntax then you will not get this kind of error.


Read More: How to Check If Value Exists in Range in Google Sheets (4 Ways)


If you make a mistake while typing the function name then you will get a #NAME? Error. Follow the steps below to see the error and know how to avoid or resolve it.

  • First, enter the following formula in a cell.
  • As you can see, there is a typo in the function name. This is why the output is showing a #NAME?

VLOOKUP #NAME? Error in Google Sheets


  • If you put the function name correctly like the below-mentioned formula then this error will not occur.

5. #ERROR! Error

There is another type of error that occurs during using the VLOOKUP function, that is #ERROR! Error. When there is a parenthesis error in the formula, then this error is displayed. Read the following steps to know the error and its solution.

  • First, insert the following formula selected cell.
  • Then, you will see the #ERROR! error in return.
  • Now, if you check properly, you will see there are double parentheses in the formula. This is the reason for the error.

VLOOKUP #ERROR Error in Google Sheets


Put parenthesis properly in the formula like below to avoid parse errors.


Things to Remember

  • Please keep in mind, the index of the VLOOKUP function should never be 0. As it means the number of a column so it should be any number other than zero.
  • In the VLOOKUP function, the lookup value must be in the leftmost or first column in the given reference range.


We have tried to show you the VLOOKUP error in Google Sheets. Hopefully, the examples above will be enough for you to understand the errors of the function and their solutions. You can practice using the sample spreadsheet. Please use the comment section below for further queries or suggestions. You may also visit our OfficeWheel blog to explore more about Google Sheets.

Related Articles



Hello everyone! This is Maruf Hasan. I am working as a Content Developer at Officewheel. Here we make content on google sheets. We share simple methods to make your google sheets journey enjoyable. I love solving problems, researching, and writing.

We will be happy to hear your thoughts

Leave a reply