*How to do a vlookup without errors in Google Sheets? Take a closer look at the most common mistakes made when working with the VLOOKUP function.*

Google Sheets VLOOKUP is one of the most useful yet trickiest functions. It helps finding matching data across multiple sheets. However, there are cases when your formula may return errors like *#N/A*, *#NAME?*, *#VALUE!*, and *#REF!*.

Let's try and answer the following question together, "Why my VLOOKUP isn't working?"

The first reason is that there are simply no matches in the range you indicated. You're powerless here and can't affect the result.

Other cases mean that you've made a mistake somewhere in the formula. Let's take a closer look at what may have gone wrong.

Delimiters are those characters that are used in formulas as separators. For example, if you are in the UK or the US, chances are your formulas require commas as delimiters:

`=VLOOKUP(A2,$F$2:$G$10,2,FALSE)`

And it is your spreadsheet locale that controls the separator choice. Different locales call for different separators.

To check your locale in Google Sheets, go to *File > Spreadsheet settings*. You'll see the locale under the *General* tab:

To understand what delimiters your VLOOKUP needs based on your locale, just start entering the function. Google Sheets will show you how your formula should look exactly and what separators must be used:

If the delimiters are all correct but your Google Sheets VLOOKUP still fires errors, check other possible issues and their solutions below.

The formula can't find anything and returns the *#N/A* error?

The simplest solution is the best here – most likely, you entered the data incorrectly. This may happen especially if you type the formula directly into the formula bar rather than the cell.

If you see the *#NAME?* error, most likely you wrote the function name incorrectly – missed or misspelt a letter or two.

Check that and the VLOOKUP syntax just in case, correct the typos, and everything will work.

The formula worked just fine until you added a few rows or columns into the table and the *#N/A* error has appeared out of the blue?

Check if the search range and the cell with the value to look for are entered correctly.

If you use relative cell references (e.g. A1) instead of absolute ones (e.g. $A$1) and then modify the table (e.g. added a column), the data will shift, the references will change, and the formula will refer to wrong cells:

I added the "Article" column. The "Price" column is not included in the range anymore, thus the price cannot be found.

If that happens, simply *Undo* the table modifications and fix the references.

You can use the *Formulas* tool in the Power Tools add-on to convert all relative references to absolute in a couple of clicks:

Your VLOOKUP formula is set to return approximate match (TRUE), while the lookup column is not sorted.

To fix that, either sort the lookup column A to Z or change TRUE to FALSE.

You may have forgotten that the VLOOKUP function in Google Sheets always searches in the first (leftmost) column of the lookup range. If the values for the *search_key* argument are in the second or some other column, VLOOKUP won't work and will return the *#N/A* error:

Sometimes the third argument of Google Sheets VLOOKUP is indicated incorrectly.

It cannot be less than 1 and more than the total number of columns in the search range. If the number is incorrect, VLOOKUP will return the *#VALUE!* error.

If that happens, please count the number of columns in the entered range and make sure it includes the number from the third argument of the VLOOKUP function.

The search range can be in another table or even another Google spreadsheet.

You'll notice that something's off if you see the *#REF!* error.

It means that the range you put into the formula is incorrect and the function cannot locate it.

In other words, the format of the cell with a required value (D3 in our case) and the format of the leftmost column in the search range (A3:A21 in our case) differ (numeric and textual).

This may happen when you use numeric codes instead of textual entries (order numbers, goods IDs, dates, and other identifiers).

As you the example above shows, the values look the same but the entry in D6 is formatted as a number while A10 contains text. A text cannot be treated as a number, making the VLOOKUP function return the *#N/A* error in Google Sheets.

How to quickly solve this problem?

- You can add the
**TEXT**function that will transform any values into text:

I used the following formula:

`=VLOOKUP(TEXT(D6,"#"),$A$3:$B$21,2,FALSE)`

Where I replaced the usual cell reference to D6 with another function:

`TEXT(D6,"#")`

The arguments of the

**TEXT**function are very simple:- The first argument,
*number*, indicates the cell to format. - The second argument,
*format*, is a pattern to which I'm going to convert the value of the cell.

- The first argument,
- If there's only one error like this, you can fix it quicker by changing the cell's number format. Select the cell and go to
*Format > Number > Plain text*in the Google Sheets menu. Its contents will be changed into the text:

As you can see, the error has gone since now we're looking for the textual value among other textual values.

The function is unable to find the required value since the cell contains excess spaces or non-printing chars (line breaks and others).

In my example, VLOOKUP returns the error because there are two spaces typed into D4 accidentally. And since the function compares symbols, the search has obviously failed:

This error may occur quite often and is almost impossible to catch visually. For example, if the value consists of two words, an excess space may find its way in between the words.

You may also enter by mistake such non-printing characters that are hidden on the screen (tabulations, line breaks, etc).

Here are the solutions:

- The textual functions
**TRIM**and**CLEAN**will help and remove unwanted invisible characters:`=VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,FALSE)`

The

**TRIM**function removes extra spaces while the**CLEAN**function deletes all non-printing characters.**Tip.**You can always use this combination of functions "just in case" to avoid any errors. - Or use the Remove tool from Power Tools to delete all unwanted characters at once:

I think you will agree on the fact that errors don't make your table look better. Is there a way to get rid of them?

Well, yes, but it won't hurt to first double-check if your VLOOKUP formula in Google Sheets was built correctly:

- Start with the lookup range. Did you put it correctly? Does your value appear in the leftmost columns of this range?
- Make sure the "
*is_sorted*" argument is correct: TRUE or FALSE, depending on the match you need and the sorting of your range. - Check for all possible number formats mismatches and extra spaces or non-printing chars.

If everything looks fine yet the formula doesn't return the value, add the **IFERROR** function there:

`=IFERROR(VLOOKUP(D9,$A$3:$B$21,2,FALSE),"No items")`

The syntax of **IFERROR** is easy to understand:

*value*– something we check for errors. If everything's fine, the result of our calculation returns.*[value_if_error]*– something the function returns if the first argument has caught an error.

In my case, F9 shows "*No items*" because VLOOKUP can't find any matches.

This is how you check your VLOOKUP function for errors in Google Sheets. If you have any questions, please ask them in the comments section below.

## 16 responses to "If VLOOKUP in Google Sheets is not working"

Hi, I cannot get VLOOKUP to work in sheets. If I import an excel file with exactly the same formula to sheets it works perfectly, but if I put the same formula into the google sheets version of the documentis gives me a parse error. I think it may have something to do with regional configurations as I am in Argentina, but use sheets in English. I have also tried using the spanish formula BUSCARV which made no difference.

Any thoughts?

Thanks,

Ed

Hi Ed,

Open

File > Spreadsheet settingsin the Google Sheets menu and set a locale that you'd like to use.Once set, please enter the formula again with correct symbols that separate arguments (commas or semicolons, you'll see the required symbols in the formula hint).

Your VLOOKUP should work then.

Hermano! Do the query in a column and return a value in a column "after" the lookup column, like =VLOOKUP(A1, A3:B10, 2, FALSE). It did not worked while I was using column "1" as return value. At least this worked for me. If this is the case.

I am using VLOOKUP and it is working perfectly except that the hyperlink isn't carrying over anymore. I work at a school and I set up the google sheets as I did last year, the hyperlinks carried over and are still working. Can you help?

To be clear the hyperlinks that are working are from the sheets that I set up last year.

Nikki,

could you please specify if your hyperlinks are in the same file where you try to reference them? Or if you're trying to pull them from a completely different file?

Thank you for helping out my 1-hour frustration!

Hi all,

Please help!

I'm using vlookup and it's working for half of my values - for numbers 1-96 it gives me a value , but then gives me an error for any values about 97, even though the formula is exactly the same and the chart has all numbers from 1-160. any ideas as to why?

Hi Chloe,

I'm afraid it's hard to tell what's going wrong without seeing the structure of your table and the data you use there. Please consider sharing your file with us (support@4-bits.com) with your source data and the result you expect to get.

Note.We keep that email for file sharing only, please do not email there. Once you share the file, just confirm by replying here.thank you too match

I benefited from it

Greetings,

I run into an odd problem. Consider 2 columns X 9 row of text data, first column names, second family name. If I search using the name I get the following: first name return last family name, 2nd returns 2nd, 3rd returns 3rd, 4th returns last, 5th and 6th return last, 7th and 8th return and error and last returns 3rd!! Try to make sense of that?!?

Just got my answer: The data was not sorted...I can'T sort the data everytime I wonder How I can fix this.

Hello Patrick,

Please try the solutions from this part of the article.

Thank you very much!

Hi, i dont know why but this is wrong

=VLOOKUP(C2,PartyTable,2,FALSE)

In this sheet

https://docs.google.com/spreadsheets/d/1WTanx1OfLF99oD9f8EMnlQv0iE6AtW5vEiRn9q8Gwuo/edit?usp=sharing

Thanks for helping

Hi Mauro,

You're using wrong delimiters. We described this problem and the way to fix it here, please have a look.