The VLOOKUP function is one of Microsoft Excel’s most powerful and widely used functions. VLOOKUP, short for “Vertical Lookup”, it allows you to search for a specified value in the first column of a table (array) and return a value from a specific cell in the same row from another column. When used properly, VLOOKUP can be a massive time-saver in data handling. But let’s be honest, sometimes it doesn’t work like you want it to. If you are encountering a #N/A error or unexpected results, it can be incredibly frustrating for you.
If you are having this issue “Why is my VLOOKUP not working?, don’t worry; we are here to help you with a solution. In this guide, we’ll take you through the five most common reasons that VLOOKUP fails and the simple, common-sense fixes that you can use for each issue.
1. VLOOKUP #N/A Error: You are Looking For an Exact Match
The #N/A error in Excel’s VLOOKUP function often occurs when you’re looking for an exact match, but something prevents the function from finding it. If you encounter this error, this could be due to looking for an exact matching value, and VLOOKUP doesn’t find the exact matching value, it returns an #N/A error. However, if you use the approximate matching mode, the VLOOKUP function will look for the Exact matching value first, and if it does not find it, it will look for the nearest smallest value in the data. If you are looking for the exact matching value, you should type FALSE at the end of your formula, so Excel knows you’re looking for that one specific value.
Now, here’s something important: this part of the formula is optional, meaning you can skip it. But if you don’t write anything, Excel will automatically use TRUE instead. And when TRUE is used, Excel won’t look for an exact match, it will look for the closest match it can find. And then VLOOKUP will return the wrong value rather than the exact match you are looking for. For that to work correctly, your data must be sorted in ascending order (like A to Z or 1 to 100). If it isn’t sorted properly, Excel might give you the wrong answer.
In some cases, when this part is left out and the data isn’t sorted, the VLOOKUP formula can return a completely incorrect result, which can be very confusing if you don’t realize what’s going wrong.
Solution:
So, to manage this error and overcome the incorrect results returned by the VLOOKUP function, you must first use the correct spellings for the exact matching lookup values. Excel, for example, does not consider “Apple” and “apple” to be similar.
Second, you can use the TRIM function to remove extra spaces or clean your data.
=TRIM(A2)Thirdly, you have to set the fourth argument of VLOOKUP to FALSE, and it will then return the exact matching value instead of giving a #N/A error or giving the wrong value in the results.
2. The Lookup Column Is Not the First Column or You Have Inserted a Column
The other most common reason for your VLOOKUP not working could be that your lookup value is not in the first column. We all know that the VLOOKUP function only works from right to left and cannot look up for a value from its left side or other columns than the first one. VLOOKUP searches down the first column of your given range. VLOOKUP will give you false results and will return errors as a result if you ever try to look up a value in other columns rather than the first column.
Also, the VLOOKUP method does not operate if you have first created an empty column, either by accident or on purpose. It will analyze the first data column and search for the value down until it finds what you are looking for.
Solution:
The possible solution to this error is that you should always look up a value in the first column and remove any empty columns.
The second method is to use INDEX-MATCH, which is more flexible and powerful. You can use this combination in the VLOOKUP function because it allows you to search for a value from any column.
=INDEX(C2:C10, MATCH("Apple", B2:B10, 0))3. Your Data Table has increased
The other reason VLOOKUP cannot work is that you have added more rows to the data table in which you are looking for a certain value. But you haven’t adjusted the formula to add up the rows in the cell ranges. If this occurs, your VLOOKUP doesn’t work and will return errors.
Solution:
The solution to this problem is simple: You just have to add the additional rows to the VLOOKUP formula as cell ranges. Make sure that the cell ranges cover the entire table in which you are searching for a value.
The second possible solution to this problem is to turn the lookup range into a table. You only need to select the data range and then go to the Insert Tab to turn it into a table. When you convert to a table and add additional rows of data, VLOOKUP will consider them to be part of the lookup range. VLOOKUP will then use the table as the lookup range rather than the specific cells.
You can see in the image that after changing the cell ranges from A2:C7 to A2:C10, the complete data table has been selected.
4. VLOOKUP Not Matching Text: The Data Types Don’t Match
When VLOOKUP does not match text because the data types in the lookup value and the data table don’t match, the problem usually stems from differences in formatting (e.g., text vs. numbers) or trailing spaces. Here are steps to troubleshoot and resolve the issue:
1. Verify Data Types
Check if the lookup value and the table column are the same data type (text or number).
- In Excel, use the
=TYPE(cell)function to check the data type of a cell:- Returns
1for numbers. - Returns
2for text.
- Returns
- Ensure both the lookup value and the table column are the same type.
2. Convert Numbers Stored as Text
Convert Text to Numbers:
- Select the range with numbers stored as text.
- Use the “Text to Columns” tool in the Data tab:
- Highlight the range.
- Go to
Data>Text to Columns> Finish.
- Or multiply by 1 (
=A1*1) or add 0 (=A1+0) in a helper column and copy-paste as values.
Convert Numbers to Text:
- Use the
TEXT(cell, "0")formula. - Alternatively, use
=TEXT(cell, "@").
3. Remove Extra Spaces
Use the TRIM function to clean up extra spaces in both the lookup value and the table column:
- In a helper column, enter
=TRIM(A1)to remove leading, trailing, and extra spaces. - Replace the original data with the trimmed data.
4. Ensure Consistent Formatting
Check for consistency in formats:
- Select both the lookup value and the table column.
- Format both as “General” or “Text” (
Hometab >Numberdropdown).
5. Handle Non-Printable Characters
Use the CLEAN function to remove hidden characters from the data:
- In a helper column, enter
=CLEAN(A1). - Replace the original data with the cleaned data.
6. Force Exact Matches
Ensure your VLOOKUP formula includes the exact match argument:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)FALSE forces Excel to look for an exact match.
7. Check for Hidden Characters
Sometimes, data may contain hidden characters (e.g., non-breaking spaces). Use the CODE function to identify these:
=CODE(MID(A1, 1, 1))checks the first character.- Remove the offending characters using
SUBSTITUTE:=SUBSTITUTE(A1, CHAR(160), "")(to remove non-breaking spaces).
8. Manually Re-enter Values
If the above methods fail, re-entering the data manually in both the lookup value and table column can resolve the issue.
Example
Problem: Lookup value is “123” (text), but the data table has 123 (number).
- Convert Lookup Value to a Number:
=VALUE("123") - Convert Table Column to Text:
=TEXT(A1, "0") - Use the adjusted data in your VLOOKUP.
Final Formula:
=VLOOKUP(VALUE(A1), table_array, 2, FALSE)In the solution, you can convert these numbers to actual numbers by using the TEXT() and VALUE() functions. After that, your text-sorted numbers will be converted to real numbers, and you will be able to see results rather than errors.
5. The Range Isn’t Locked When Copying the Formula
Another major issue that causes VLOOKUP to not perform properly is moving the VLOOKUP formula between rows or columns. When you do that, your range might change unintentionally. In Excel, when you are copying a formula, it causes relative references to update based on the new location. If your table range changes, the lookup will not function properly.
In the image above, you can see that the formula produces the right results.
And when you drag and drop the same formula for the cell F3, Excel not only updates the lookup value from E2 to E3, but it also updates the lookup range, which causes the issue.
Solution:
A solution to this problem is to lock your lookup table (the table in which you are looking up information), also known as table_array. To lock your lookup table, you can use dollar signs $.
=VLOOKUP(E2, $A$2:$C$10, 2, FALSE)
Now you can see that the lookup range has not changed when dragging the formula down to cell F3.
6. VLOOKUP #REF Error: The Column Index Number Is Incorrect
The third argument in VLOOKUP is the column index number, which is the number of the column in the table array from which to get a value. If you enter a column number that does not exist in your range, you will receive an error or an inaccurate result.
As you can see in the above image, the column-index-number is set to 4. And the D column is empty, so it returns an error.
Solution:
To fix this error, simply check and correct your column index number, just like below:
7. VLOOKUP Not Working: Data Contains Duplicates and Non-Unique Values
Another common cause why your VLOOKUP isn’t working is that your data table contains non-unique and duplicate values, as shown in the image.
VLOOKUP returns only the first match that it finds. So, if your lookup column contains many entries with the same value, Excel will always return the value from the first one, even if there are others with different values.
Solution:
The first and simplest solution to this problem is that you can remove duplicates by clicking the Remove Duplicates option on the Data tab.
The second solution for you can be to use newer functions such as FILTER() (available in Excel 365) or complex formulas with INDEX to return multiple matched values.
8. Your Lookup Value is On the Left Side
VLOOKUP only works when the value you’re searching for is in the first (leftmost) column of your selected table range. If your lookup value is in a column to the right of the return column, VLOOKUP won’t work and will return an error. As you know that VLOOKUP can only look up from left to right; it can’t search to the left of the data table. So if you try to search for a value in Column C and return a result from Column A, VLOOKUP will fail.
Solution:
To avoid this error, you can move the lookup column to the left side of your dataset.
And the second option is that instead of using VLOOKUP, you can combine the INDEX and MATCH functions. This combination lets you look up in any direction. INDEX-MATCH allows you to find values independent of their position (left or right), making it more versatile than VLOOKUP.
The Third solution for this problem can be using the XLOOKUP function, which is more advanced. Using the XLOOKUP function, you can look up in any direction and will encounter fewer issues than with VLOOKUP.
Bonus Tips to Avoid VLOOKUP Errors
Sort Carefully:
If you are using approximate match (by setting the fourth argument to TRUE), make sure the first column is sorted in ascending order. If you are using the exact match, make sure to set the fourth argument to false. Otherwise, it will return a #N/A error if it does not find the exact matching value in the data table.
Use Named Ranges:
Always make sure to use name ranges when working with the VLOOKUP function. And when you use named ranges, this will make your formulas cleaner and easier to understand.
Try XLOOKUP:
If you’re using a newer version of Excel, the XLOOKUP function is a more modern, flexible alternative to VLOOKUP. In XLOOKUP, you will not encounter these problems that you might face using other lookup functions like VLOOKUP.
VLOOKUP Not Working: Conclusion
If you’ve ever felt stuck and thought that VLOOKUP is not finding the exact matching data or why you’re getting wrong results, you’re not alone. Whether it’s a VLOOKUP not matching text issue or even VLOOKUP returning the wrong value, the truth is, a small mistake in your formula or data structure can throw everything off. But now that you know what to watch out for and how to fix these common issues, you’ll find it much easier to troubleshoot whenever VLOOKUP is not working. Just remember to check your ranges, watch your column index, handle text and duplicates carefully, and don’t hesitate to try functions like INDEX-MATCH or XLOOKUP for more flexibility.
