If VLOOKUP in Google Sheets is not working

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.

VLOOKUP is one of the most useful yet trickiest functions in Google Sheets. 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?"

Most common VLOOKUP mistakes

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.

Incorrect data

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

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

Incorrect function name

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

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

Incorrect VLOOKUP cell references

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:
Relative cell references pull incorrect values.

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:
Convert relatives to absolutes with Power Tools.

Incorrect "is_sorted" argument

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

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

Incorrect lookup column

You may have forgotten that the VLOOKUP function 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:
Make sure to indicate correct lookup column.

Note. Please keep in mind that VLOOKUP cannot look at its left.

Incorrect column number

Sometimes the third argument of the VLOOKUP function 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.

Note. The number of the column should be 1 or more. Of course, it's unlikely that you'll enter 0 or -1, but if you use some other functions to get the serial number of the required column, you may stumble upon the 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.

Incorrect reference to another table

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.

Incorrect Number format

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).
The #N/A error for when cells' formats differ.

As you can see on the example above, 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.

How to quickly solve this problem?

  1. You can add the TEXT function that will transform any values into text:
    Convert number format to text format directly in the formula.

    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.
  2. 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 > Plaint text in the Google Sheets menu. Its contents will be changed into the text:
    Change the Number format of the cell.

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

Excess spaces and non-printing characters

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:
Excess spaces make the function return the error.

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:

  1. The textual functions TRIM and CLEAN will help and remove unwanted invisible characters:

    =VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,FALSE)

    Use TRIM and CLEAN to remove all excess characters.

    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.
  2. Or use the Remove tool from Power Tools to delete all unwanted characters at once:
    Get rid of extra spaces and non-printing chars with Power Tools.

Replace VLOOKUP errors with your own text

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 the VLOOKUP formula was built correctly:

  1. Start with the lookup range. Have you indicated it correctly? Does your value appear in the leftmost columns of this range?
  2. Make sure the "is_sorted" argument is correct: TRUE or FALSE, depending on the match you need and the sorting of your range.
  3. 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")

VLOOKUP and IFERROR to trap possible errors.

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 you VLOOKUP function for errors in Google Sheets. If you have any questions, please ask them in the comments section below.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com