# [Solved!] VLOOKUP Function Is Not Working in Google Sheets

The VLOOKUP function is a dynamic tool in Google Sheets. We can search for any values in any given range with the help of this function. But sometimes you might find that the VLOOKUP function is not working in Google Sheets. In this article, I’ll discuss 8 easy fixes when the function is not working with clear images and steps.

## 8 Solutions When VLOOKUP Function Is Not Working in Google Sheets

Let’s get introduced to our dataset first. Here we have some products in Column B and their prices in Column C. With the help of this dataset, I’ll show you 8 useful solutions when the VLOOKUP function is not working in Google Sheets. ### Solution 1: Checking Delimiters

The delimiter is the sign we use when we enter any function in Google Sheets. Normally we use a comma (,) as our delimiters. Using wrong delimiters will give a #N/A Error. From our dataset, we want to look for the price of the Mouse. So, in our dataset, we have used this formula-

`=VLOOKUP(B13.B4:C10.2.FALSE)`

As we have used dot sign (.) as our delimiters the output is giving a #N/A Error. Let’s see how to correct it. Steps:

• Firstly, type the following formula with the right delimiters (,) in Cell C13
`=VLOOKUP(B13,B4:C10,2,FALSE)`
• Then hit Enter to get the output. ### Solution 2: Inserting Function Name Properly

We have to put the function name properly to get the desired result. So, check for any spelling mistakes you may have made during applying the VLOOKUP function. In our dataset, we have put the formula shown below-

`=VLOKUP(B13,B4:C10,2,FALSE)`

As we have done a spelling mistake it is giving a #NAME? error. Steps:

• First, write the next formula in Cell C13
`=VLOOKUP(B13,B4:C10,2,FALSE)`
• Next, press Enter to get the result correctly. ### Solution 3: Giving Cell Reference Correctly

Giving correct cell references is also important during applying the VLOOKUP function. A cell reference is a value that the function searches within the given range of data. Like in this case we have given Cell E6 as a reference whereas our desired value is in Cell B13. That’s why applying the formula will give a #N/A Error

`=VLOOKUP(E6,B4:C10,2,FALSE)` Steps:

• First of all, insert the below formula in Cell C13
`=VLOOKUP(B13,B4:C10,2,FALSE)`
• After, hit the Enter Button to get the correct output. ### Solution 4: Matching Range Properly

You should also put the data range correctly in the VLOOKUP function. Otherwise, it’ll give a #N/A Error. Like in this case we have a new column titled Product ID in Column C. So, the prices are moved to Column D. Then when we apply the following formula it is giving an error-

`=VLOOKUP(B13,C4:D10,2,FALSE)`

It is giving an error because the function searches for the value Mouse in the range from Cell C4 to D10 which is the false range. Because VLOOKUP always searches through the first column of the range. Let’s correct it. Steps:

• In the first place, put the formula in Cell C13
`=VLOOKUP(B13,B4:D10,3,FALSE)`
• After that, press the Enter Button to get the result instantly. • Don’t forget to put the correct column index number which is 3 because we have our prices in column no 3 of our data range.

### Solution 5: Inserting Correct Column Index Number

If we insert the wrong column index number then the VLOOKUP function will return the wrong value in Google Sheets. Like in our case we have inserted the formula like this-

`=VLOOKUP(B13,B4:D10,2,FALSE)`

The formula is returning the product id of the product but we want the price of the product. As we have put the column index no 2 it is giving values from column no 2, Column C. Steps:

• In the beginning, type the following formula in Cell C13 with the correct column index no 3-
`=VLOOKUP(B13,B4:D10,3,FALSE)`
• Thereafter, hit Enter to get the output. ### Solution 6: Removing Extra Space

We may have some extra spaces in our lookup value. Then the VLOOKUP function won’t work. Below the formula isn’t working because we have some spaces in Cell B13 as shown in the picture. We have applied the next formula and it isn’t working for having space in the value-

`=VLOOKUP(B13,B4:D10,3,FALSE)` #### 6.1. Manually Removing Space

We can remove the space manually like below.

Steps:

• Before all, write the next formula in Cell C13 after removing spaces from Cell B13
`=VLOOKUP(B13,B4:D10,3,FALSE)`
• Afterward, press Enter to get the price. #### 6.2. Uniting VLOOKUP, TRIM, and CLEAN Functions

Moreover, we can merge the VLOOKUP, TRIM, and, CLEAN functions to get results instantly. In this case, we don’t have to manually remove any space. The output is very fast.

Steps:

• Earlier on, insert the formula in Cell C13
`=VLOOKUP(TRIM(CLEAN(B13)),B4:D10,3,FALSE)`
• Consequently, click Enter to get the desired output. Formula Breakdown

• CLEAN(B13)

At first, this function cleans any non-printable characters from Cell B13.

• TRIM(CLEAN(B13))

Then this function trims any space present in Cell B13.

• VLOOKUP(TRIM(CLEAN(B13)),B4:D10,3,FALSE)

Finally this formula search for the value in Cell B13 from the data range Cell B4 to D10 and returns the output from column no 3, Column D.

### Solution 7: Putting Right Format

When we search for something with the VLOOKUP function the format should be similar for both value and dataset. Otherwise, we don’t get any results. Now in our dataset, we have product ids in text format but our given value in Cell B13 is in number format. So our next formula isn’t working here-

`=VLOOKUP(B13,B4:C10,2,FALSE)`

So we have to put a similar format when the VLOOKUP function is not working with the text format. #### 7.1. Applying Format Command

We can do this simply by applying the Format Command.

Steps:

• Initially, Select Cell B13 and go to Format > Number > Plain Text. • Again, put the formula in Cell C13
`=VLOOKUP(B13,B4:C10,2,FALSE)`
• Moreover, hit the Enter button to get the desired price. #### 7.2. Combining VLOOKUP and TEXT Functions

Additionally, we can combine the VLOOKUP and TEXT functions to get the result instantly.

Steps:

• Before, type the following formula in Cell C13
`=VLOOKUP(TEXT(B13,"#"),B4:C10,2,FALSE)`
• After, click the Enter button to get the result. Formula Breakdown

• TEXT(B13,”#”)

At first, this function converts the format of Cell B13 into text.

• VLOOKUP(TEXT(B13,”#”),B4:C10,2,FALSE)

Finally this formula search for the value in Cell B13 from the data range Cell B4 to C10. Then it gives the output from column no 2, Column C.

### Solution 8: Inserting Sheet Name Correctly

Sometimes the VLOOKUP function does not work between sheets. We have a dataset like below which have products in Column B, their ids in Column C, and prices in Column D. We gave a name to our dataset which is Datasheet. When we move to another sheet and apply the following formula it gives an #REF Error

`=VLOOKUP(B5,Sheets!B4:D10,3,FALSE)`

This error occurs because here we put the sheet name wrong. Steps:

• Firstly, type the following formula in Cell C13 with the correct sheet name-
`=VLOOKUP(B5,Datasheet!B4:D10,3,FALSE)`
• Finally, hit Enter to get the output instantly. ## Conclusion  