**VLOOKUP **is one of the most common and useful functions. As it is widely used so many people have complaints about not working **VLOOKUP** correctly or showing incorrect results. Though **VLOOKUP **has some limitations yet most of the error we get is for not understanding the syntax properly or not carefully using it. In this article, I’ll explain why **VLOOKUP **not working.

To make the explanation understandable I’m going to use a dataset that represents product information about a particular fruit shop. There are 5 columns in the dataset; these are *Fruit**, Order ID, Quantity (Kg), Price, *and

**.**

*Order Date***Download to Practice**

Feel free to download the workbook from the link below.

**8 Reasons of VLOOKUP Not Working**

### 1. VLOOKUP Not Working and Showing N/A Error

In this section, I will show you why the **#N/A error** occurs while working with **the VLOOKUP function**. As well as I will suggest you the best solution to avoid **#N/A **error.

#### 1.1. Leading and Trailing Spaces

In a large datasheet, the possibility of having extra spaces is common. Also, it is hard to identify the error as you won’t get the error unless you go through the dataset carefully.

Here, I applied the **VLOOKUP **formula correctly.

First, select a cell to place your resultant value.

➤ I selected cell **I4**Then, type the following formula into the

**Formula Bar**.

`=VLOOKUP(H4,B4:F12,2)`

Here, in the **VLOOKUP **function, I selected the cell **H4 **as **lookup_value**, and selected the range **B4:F12 **as **table_array**. As I want to know the **Order ID **so given **2 **as **col_index_num**.

Press the **ENTER **key. Now, you were supposed to get the **Order ID **of the **look_up **value but it will show **#N/A**.

Now, after looking at the dataset you will find that the **lookup_value** **Apple **has some leading spaces that’s why **VLOOKUP **is not working.

**Solution:**

To remove extra leading or trailing spaces, use the **lookup_value **argument with **the TRIM function** within the **VLOOKUP **function.

Let me show you how you can use the **TRIM **function within the **VLOOKUP **function.

To avoid the **VLOOKUP **error type the following formula in your selected cell.

`=VLOOKUP(TRIM(H4),B4:F12,2)`

Here, the **TRIM **function will remove all existing leading and trailing spaces of the selected cell **H4**.

#### 1.2. For Typo Mistake VLOOKUP Not Working

Typing mistake of **lookup_value **is another reason for not working **VLOOKUP**.

Here, you will see I inserted the formula correctly in the selected cell.

`=VLOOKUP(H4,B4:F12,2)`

Press the **ENTER **key but instead of showing **Order ID**, it will show you a **#N/A **error.

Now, look at the **lookup_value** you will see that the spelling of **Apple **is incorrect, that is the reason **VLOOKUP **is not working.

**Solution:**

Always carefully type the **lookup_value**. You have to maintain the exact spelling of the value from the data table.

As I typed the **lookup_value **as it is in the table so **VLOOKUP **is working.

#### 1.3. Numeric Value Formatted as Text

In case the numeric values are formatted as text in a **table_array** then it will show you a **#N/A **error while using the **VLOOKUP **function.

I will try to get the **Price **by using the **Order ID **as **lookup_value**.

First, select a cell to place your resultant value.

➤ I selected cell **I4**Then, type the following formula into the

**Formula Bar**.

`=VLOOKUP(H4,C4:F12,3)`

Press the **ENTER **key. Thus, you will get the **#N/A **error instead of **Price**.

Now, if you go through the **Order ID **column then you will see the number **1001 **is formatted as text. That’s the reason for not working **VLOOKUP**.

**Solution:**

To avoid such types of errors, always check the format of the numeric values. Here, I corrected the numeric format as number so the **VLOOKUP **is working.

**Read More:** **VLOOKUP Partial Text from a Single Cell in Excel**

**1.4. Lookup Value is Not the Leftmost Column**

The **VLOOKUP **function maintains a sequence, which is the **lookup_value** has to be the *leftmost column*, if not then it won’t work.

I will try to get the **Price **by using the **Order ID **as **lookup_value**.

So, I used the following formula.

`=VLOOKUP(H4,B4:F12,3)`

But here the **Order ID **column is not the leftmost column of the **table_array B4:F12** that’s why it is showing **#N/A **error.

**Solution:**

Here you can avoid the error in 2 ways.

⏩ One is you can change the **table_array **where the **lookup_value **will be the leftmost column.

⏩ Second, you can place the **lookup_value** column at the leftmost position of the dataset table.

**Read More:** **VLOOKUP with Two Lookup Values in Excel (2 Approaches)**

**1.5. Oversized Table or Inserting New Row & Column with Value**

Sometimes we insert new data into our dataset but forget to change the **table_array** then the **VLOOKUP **can’t work properly.

I will try to get the **Order ID **by using the **Fruit **as **lookup_value**.

So, I used the following formula.

`=VLOOKUP(H4,B4:F12,2,FALSE)`

Here, I used the **exact match **type to avoid misleading information and also inserted information for **Lichi **yet got an error because I didn’t update the **table_array**.

**Solution:**

Remember whenever you insert new data to your dataset table update the **table-array **also.

⏩ Here, I updated the **table_array **in the formula.

`=VLOOKUP(H4,B4:F14,2,FALSE)`

⏩ Another way is converting your dataset into a table.

First, select the cell range.

Then, open **Insert** >> select **Table**

A** dialog box **will pop up.

Then, click **OK**.

As your dataset is now turned into a table you just can use the table name.

**Read More:** **Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)**

**2. VLOOKUP Not Working and Showing VALUE Error**

From this section, you will get to know why the **#VALUE error** occurs while working with the **VLOOKUP **function. Also, I will suggest you the all-possible solution to avoid **#VALUE **error.

#### 2.1. For Column Index Number Less than 1

If you mistakenly use the **col_index_num** less than **1**, then you will get **#VALUE **error.

In case you get this **#VALUE** kindly check your **col_index_num** argument.

**Read More:** **VLOOKUP with Numbers in Excel (4 Examples)**

#### 2.2. Using More than 255 Character

Suppose you have a long text as a value which is more than **255 **characters then you will have **#VALUE **error.

Here, in the **A7 **cell, I inserted a value exceeding **255 **characters.

Then, used the following formula

`=VLOOKUP(G4,A4:E12,2)`

Now, you can see that the result is showing the **#VALUE **error.

**Solution:**

To avoid this error either you can reduce the character or you can use **the INDEX** and **the MATCH functions** instead of **VLOOKUP**.

Here, I used the **MATCH **and the **INDEX **function.

`=INDEX($B$4:$B$12,MATCH(TRUE,INDEX($A$4:$A$12=G4,0),0))`

Here, in the **INDEX **function selected the absolute reference of the cell range **$B$4:$B$12 **from where I want to return value.

In the **MATCH **function, given **TRUE **as **lookup_value **and used another **INDEX($A$4:$A$12=G4,0) **function as **lookup_array** then used taken **0 **as **match_type **to use **Exact Match**.

Press the **ENTER **key and you will get the result for the **lookup_value **of more than **255 **characters.

**Read More:** **INDEX MATCH vs VLOOKUP Function (9 Examples)**

**Similar Readings**

**Excel LOOKUP vs VLOOKUP: With 3 Examples****How to Make VLOOKUP Case Sensitive in Excel (4 Methods)****Excel VLOOKUP to Find Last Value in Column (with Alternatives)****How to Perform VLOOKUP with Wildcard in Excel (2 Methods)****Excel VLOOKUP to Return Multiple Values Vertically**

**3. VLOOKUP Not Working and Showing REF Error**

Here, you will know why the **#REF error** occurs while working with the **VLOOKUP **function and also, you’ll get the solution to avoid **#REF **error.

#### 3.1. Using Column Index Number Greater Than Table

In case you use the **col_index_num **greater than the number of columns you have in the **table_array **then you will get **#REF** error.

Here, I’ve used **6** as **col_index_number** but the **table_array **has **5** columns in total that’s why the **VLOOKUP **function is not working and showing **#REF **error.

**Solution:**

To avoid **#REF **error checks the **col_index_num** and use the number which is in the **table_array**.

**Read More:** **VLOOKUP to Return Multiple Columns in Excel (4 Examples)**

### 4. VLOOKUP NAME Error

Let me show you why the **#NAME error** occurs and how you can remove it.

#### 4.1. For Misspelling Function Name VLOOKUP Not Working

The **#NAME **error comes for the misspelling of functions name.

**Solution:**

To avoid **#NAME **errors always use the appropriate function name from the Excel built-in function.

### 5. Using Approximate Match

If you use **approximate match (TRUE)** then there stays a possibility of either **#N/A **error or **incorrect **result.

I will try to get the **Order ID **by using the **Fruit **as **lookup_value**.

So, I used the following formula.

`=VLOOKUP(H4,B4:F12,2,TRUE)`

But here, I gave **Lichi **as **lookup_value **and used **TRUE **as **range_lookup**. **VLOOKUP **shows **1007 **as **Order ID **which is incorrect because **1007 **is the **Order ID** of **Cherry**.

As I used approximate match so instead of showing error it shows wrong information

**Solution:**

Use the **lookup_value **carefully. Instead of using **approximate match** type you can use **exact match** type. I think getting an error is way better than having misleading information.

You can wrap up the formula with **the IFERROR function** to show any error message when it can’t find the value within the range.

**6. Table Reference Is Relative**

If your **table array** is referenced relatively then you may have error notification or error while copying the formula to **lookup** other values.

**Solution:**

To avoid this error use absolute reference.

Press the **F4 **key while selecting the reference then it will convert the **relative reference** to **absolute reference**.

Here, I used the following formula

`=VLOOKUP(I4,C4:$F$12,2)`

**7. ****VLOOKUP Not Working ****For Inserting New Column **

If you insert a new column to your existing dataset then the **VLOOKUP **function doesn’t work. The **col_index-num** is used to return information about a record in the **VLOOKUP **function. The **col_index-num **is not durable so if you insert a new one then the **VLOOKUP **won’t work.

Here, you can see the **VLOOKUP **function is working properly.

But here I inserted one new column that’s why it is showing **0 **instead of showing the expected result.

**Solution:**

⏩ To avoid such types of problems either you can protect the worksheet so that anyone can’t insert new columns but it’s not friendly enough.

⏩ Another solution is you can use the **MATCH **function within the **VLOOKUP **function.

So, type the following formula.

`=VLOOKUP(I4,B4:G12,MATCH(J3,B3:G3,0),FALSE)`

Here, in **the VLOOKUP function**, I selected the cell **I4 **as **lookup_value** then selected the range **B4:G12 **as **table_array **and as the **col_index_num **used the **MATCH **function, also used **FALSE **as **range_lookup **to get **Exact Match**.

In the **MATCH **function, I used the column name **J3 **as **lookup_value**, next selected the column name range **B3:G3** as **lookup_array** then taken **0 **as **match_type **to use **Exact Match**.

Press the **ENTER **key. Thus, you will get the expected result you want.

**8. Lookup Value Has Duplicate Values**

In case your **lookup_value **contains duplicate values then **VLOOKUP** won’t work for all the available values.

**VLOOKUP **only returns the first value that matches the value you looked for.

**Solution:**

To avoid such types of problems either you can remove the duplicates or you can use the **pivot table**.

⏩ You can remove the duplicates using the **Remove Duplicates **from then Ribbon.

⏩ Also, you can use the **Pivot Table**.

To use it,

First, select the cell range

Then, open the **Insert **tab >> select **Pivot Table**

A **dialog box **will pop up, select the place then click **OK**.

Now, you can select the **Fruit **and **Order ID **in **Rows** then it will show the existing** Order ID **of your selected **Fruit**.

**Read More:** **How to Find Duplicate Values in Excel using VLOOKUP**

**Practice Section**

I’ve provided a practice sheet in the workbook to practice these explained ways.

**Conclusion**

In this article, I tried to cover all types of scenarios of **VLOOKUP **not working along with the solution to avoid errors. These different ways will help you to work with the **VLOOKUP **function more efficiently and easily. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

## Related Articles

**VLOOKUP Example Between Two Sheets in Excel****How to Use IF ISNA Function with VLOOKUP in Excel (3 Examples)****VLOOKUP to Find Duplicates in Two Columns (2 Ways)****VLOOKUP with Multiple Matches in Excel****10 Best Practices with VLOOKUP in Excel****How to Use the VLOOKUP Ascending Order in Excel (3 Ways)****VLOOKUP and Return All Matches in Excel (7 Ways)**

please send me some lessons

tks

Hello. Tabuaka! You can check out our practice problems which should help you to get familiar with Excel works!

https://www.exceldemy.com/?page_id=214698