If VLOOKUP in Google Sheets is not working

Wrong delimiters

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 > 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 what your formula should look like 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.

Incorrect data – #N/A error

The formula can't find anything and returns the #N/A error? But you're sure there's got to be a match?

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

Incorrect function name – #NAME? error

If you see the #NAME? error, you either wrote the function name incorrectly (missed or misspelled a letter or two) or got mixed up with the syntax.

Invalid VLOOKUP cell references – #N/A error

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

Fix this #N/A error

Check if the search range and the cell with the value to look for are entered correctly – they are neither misspelled nor absent from the sheet.

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

I added the "ID" 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.

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

Incorrect "is_sorted" argument

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

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

Incorrect lookup range – #N/A error

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:

Incorrect column number – #VALUE! 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 in Google Sheets will return the #VALUE! error.

Invalid reference to another table – #REF! error

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 invalid and the function cannot locate it.

Incorrect Number format – #N/A error

This means that 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 the example above shows, the values look the same but the entry in D6 is formatted as a number while A10 contains a text string. The text cannot be treated as a number, making the VLOOKUP function in F6 return the #N/A error in Google Sheets.

Fix this #N/A error – use the TEXT function

Alternatively, you can add the TEXT function that will transform any values into text.

I use the following formula:

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

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.

Excess spaces and non-printing characters – #N/A error

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, Google Sheets VLOOKUP is not working because there are two trailing spaces typed into D4 accidentally. And since the function compares symbols, the search fails:

This problem is quite common and invisible to the eye. For example, if the value consists of two words, an excess space may find its way in between the words.

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

Fix this #N/A error – use TRIM and CLEAN functions

The TRIM and CLEAN functions that are designed for text strings 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.