In this article, we are going to see how we can resolve the issues with **VLOOKUP **in the most efficient manner possible. Being one of the most popular lookup functions, **the VLOOKUP function **has been widely used in various sectors. But **VLOOKUP **is not a flawless function. It has many issues, and we need to constantly deal with them to solve the issues with **VLOOKUP**.

Below, we are seeing a case where we actually place the lookup column in the first position of the lookup range. Without placing them in front of the range, we might get an erroneous result.

**Download Practice Workbook**

Download this practice workbook below.

**10 Probable Reasons with Solutions to Issues with VLOOKUP**

Here, we will discuss separate mainstream issues with** the VLOOKUP operation** in Excel and why they occur. Alongside this, we will also discuss how we can get rid of them in brief detail. In order to avoid any kind of compatibility issue, please try to use the Excel 365 edition.

** 1. Lookup Value Is Not Found in the First Cell**

We can see in the image below that there is an error due to the wrong column placement. Here, the lookup column should be there first. But instead of that, the column is now in the second position.

**Solution: Format the Lookup Range**

Format the Lookup range in such a way that the lookup column always stays on the Left.

- Here we placed the column in the first place in the lookup range. Where we search the Lookup Value criteria.
- After doing that, we can see that the problem is now resolved and the price is now showing in cell
**C12.**

**2. Data Missing or Not Found**

### Solution: Fill up the cell with appropriate data

Fill the original dataset or modify the criteria according to the need.

- We have seen in the criteria that the criteria are not available in the range of cells
**B5:B9.** - For this, we change the criteria to “
**Sneakers”.** - After then, we can see that the price is now placed in cell
**C12.**

**Read More:** Why VLOOKUP Returns #N/A When Match Exists

**3. Format Mismatch (Apostrophe Saved As Text)**

In the image below, we can see that there is an apostrophe in front of the text. That means that the range of cells **B5:B9 **is actually formatted as text. So we basically need to reformat the cells or remove the apostrophe in front of the text.

**Solution: Format the Cells Accordingly**

- After we remove the apostrophe, we can see that the price is now in cell
**C12.**

**Read More: **VLOOKUP Not Working Due to Format

**4. Exact Match Vs Approximate Match**

In the image, we can see that the argument in **the VLOOKUP function** is used inappropriately. So we need

**Solution: Use the Correct VLOOKUP Argument**

- In
**the VLOOKUP function**arguments, the last argument is about whether we want to have an exact match or an approximate match. - In the argument,
**False**means that the match is going to be exact, which means the criteria will match completely. Otherwise, the output will be shown as**N/A.** - If we use
**True**instead of**False,**then we can see that the match is going to be approximate, which means the match is not going to be complete. The criteria are going to be matched superficially and return column value according to that. - For example, we have used
**TRUE**as an argument in the formula. Because of that, we can see that the value for the criteria is showing the wrong value, as it is trying to match approximately.

- If we use the FALSE argument, then we can see that the correct price value is now showing. Because the match is now happening Exactly.

**5. Locked Table Reference**

In the image, we can see that the formula in cell **G5, **the reference to the lookup range, is locked.

- As the reference is not locked, what happens when we try to drag the fill handle to cell
**G7?**We will see that the output is showing incorrect values according to the criteria in the neighboring cells. - The reason behind this is that, as the reference lookup is not locked, the reference range changed as we dragged the fill handle. Which makes some values miss out in the lookup range.

**Solution: Reference to the Lookup Range Should be Locked in Cases of Multiple Criteria**

We will take the help of the functions like the **IF**, **COUNTA**, **MATCH,** and **OFFSET** functions to resolve this issue.

- To resolve this issue, we need to lock the reference in the formula.
- Enter the modified formula in cell
**G5.**

`=IF(COUNTA($B$5:$B$12)>0, OFFSET($B$5, MATCH(F5, $B$5:$B$12, 0)-1, 2), "")`

- After entering this formula and dragging the fill handle to cell
**G7,**we will see that the range of cells shows the correct values.

**6. Duplicate Values**

In the below image, we can see that we have duplicate images in the range of cells **B5:E9**. Which is now making incorrect output in cell **C12. **The issues related to this usage of **the VLOOKUP function** are now needed to resolve.

**Solution: Remove Duplicate Values**

- We can remove the duplicate values in the range of cells
**B5:E9.** - Select the range of cells
**B5:E9.** - For this, go to
**Data**>**Data Tools**>**Remove Duplicates.**

- After doing that, you will see that there is a window named
**Remove Duplicates**. - In that window, check the desired column title from where you want to remove duplicates.
- Press
**OK**after that.

- After pressing
**OK**, we will see that the duplicate values are now removed and the correct pricing values are now shown.

**7. Wrong Index Number**

If the user enters the wrong index number or an index number less than 1, we can see that there is an error in cell **C12.**

**Solution: Insert the Correct Index Number**

- Upon investigation, we found that the index number in the formula is less than 1.
- So we need to double-check the index and correct it to 4.

**8. Showing Formula As Text**

In some cases, we can notice that the formula is shown in the cell. But not the output of the formula in the cell. This is not something we want to see. So we need to solve this issue regarding this **VLOOKUP** function usage.

### Solution: Convert the formula cell to a General format

- The main issue is that the cell is in text format before we enter the formula.
- So we need to change the formula cell format to something else.
- For this, select the cell in the worksheet and go to
**Home**>**Number Group**> - This will change the cell format to
**General**. - After that, re-enter your formula, and you will see that the formula showing as text is now resolved.

- After switching the format, we can see that the formula is now showing the output value.

**Read More:** Excel VLOOKUP Function Not Calculating Automatically

**9. New Column Addition**

Here we can see that there is a **VLOOKUP **formula in cell **C12. **Now there is a price value in cell **C12 **as an output of that cell value.

- For some reason, we decided to add a new column to the worksheet.
- After inserting the new column in between columns
**B**and**D**, we can see that the formula is now showing some other values instead of the Price values in the cell. - The reason behind this is that in the formula, we denoted the third column as the return column.
- As we added a new column, the output column now becomes the 4th column, but the formula has not changed yet.

**Solution: The Reference to the Lookup Range Needs to be Updated**

- For this, we changed the formula and added it to cell

`=VLOOKUP(C11,B5:E9,4,FALSE)`

**Read More:** Excel VLOOKUP Drag Down Not Working

**10. Hidden Space**

In the following image, we can see that there is an error showing in the cell due to a space after the **T-Shirt, **which is hard to notice. But the value in the lookup range does not have any space after it, and that is why there is an error showing on the **C12.**

**Solution: Check for Any Unwanted Spaces and Remove It If Found**

- After the removal of space, we can see that the issues with the price values related to
**the VLOOKUP function**use are now solved properly.

**Things to Remember **

**Check for an exact match: VLOOKUP **performs an exact match by default. If the lookup value is not there exactly, it will return an error. Make sure the data you’re searching for matches the format and case of the lookup value.

**Check the column index number: **Ensure that you’re specifying the correct column index number as the “col_index_num” argument. This number represents the column in the data range from which you want to retrieve the value. Remember that it starts with 1 in the leftmost column.

**Consider sorting the data: VLOOKUP **requires the data to sort in ascending order depending on the column you’re performing the lookup on. If the data is not in the correct sort order, you may encounter incorrect or unexpected results. Use the “TRUE” option for approximate matches when working with unsorted data.

**Handle errors with IFERROR: **To prevent errors from displaying when **VLOOKUP **cannot find a match, wrap your **VLOOKUP **formula within **the IFERROR function**. This allows you to specify an alternative result or an error message when no match is there.

**Use absolute cell references: **When copying the **VLOOKUP **formula to other cells, ensure that the cell references are absolute (e.g., $A$1) for the lookup range and relative (e.g., A1) for the lookup value. This helps maintain the correct references as you copy the formula.

**Check for leading/trailing spaces: **

Extra spaces in cells can cause **VLOOKUP **to fail. Trim the data in both the lookup value and the lookup range to remove any leading or trailing spaces that might interfere with the match.

**Consider alternative functions: **Depending on your needs, alternatives to **VLOOKUP **like **INDEX, MATCH,** or **the XLOOKUP function **might be more suitable. These functions offer more flexibility and advanced features for lookups.

**Frequently Asked Questions**

**Q1. What are the disadvantages of VLOOKUP?**

The disadvantages of **VLOOKUP **include its limited search capabilities, the requirement for an exact match, inefficiency with large datasets, inability to handle multiple matches, fragility to changes in data structure, and lack of flexibility.

**Q2. What can I use instead of VLOOKUP?**

Instead of using **VLOOKUP**, you have two popular alternatives: **INDEX-MATCH** and **XLOOKUP**.

**INDEX-MATCH **combines the **INDEX **and **MATCH **functions. Instead of searching for a value in a single column like **VLOOKUP**, **MATCH **finds the position of the desired value in a specified column. **INDEX **then retrieves the corresponding value from another column based on the position returned by **MATCH**. This approach offers more flexibility, as you can search in any column and retrieve values from any column in the table.

**XLOOKUP **is a newer function that allows you to search for a value in a table and retrieve information from any column, similar to** INDEX-MATCH**. However, **XLOOKUP **simplifies the process by providing a single function that handles both the search and retrieval steps. It also supports approximate matches, array inputs, and multiple criteria searches.

Both **INDEX-MATCH** and **XLOOKUP **offer enhanced functionality, flexibility, and improved performance compared to **VLOOKUP**, making them popular choices for data lookup tasks.

**Q3. Which is a better VLOOKUP or INDEX match?**

**INDEX-MATCH** generally offers more flexibility, allowing you to search in any column and retrieve values from any column in the table. **INDEX-MATCH** can handle both exact and approximate matches, whereas **VLOOKUP** only supports exact matches. It can also handle multiple matches and is unaffected by changes in the data structure. In terms of performance,** INDEX-MATCH **can be faster and more efficient, particularly with large datasets. Overall, **INDEX-MATCH** provides more advanced functionality and is better than **VLOOKUP **for its versatility and power in data lookup tasks.

**Conclusion**

Here we discussed some common issues with VLOOKUP in Excel and how we can get rid of them. The problems are quite straightforward and easy to resolve. Most of the problems arise due to the exact or approximate value denotation.

## Issues with VLOOKUP: Knowledge Hub

**<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel**