Issues with VLOOKUP

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.

VLOOKUP.xlsx

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.

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.

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)

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.