Nashid
In this lesson, I’ll demonstrate 7 reasons and solutions why VLOOKUP returns #N/A even though a match Exists in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
What is VLOOKUP in Excel?
VLOOKUP is a built-in Excel function that allows you to search for a specific value in one table and return a corresponding value from another column in the same row of the table. It stands for “vertical lookup.”. A very handy feature to find any corresponding value of any particular known value. .
Syntax of the function:
=VLOOKUP(what you want to look up, where you want to look for it, column number of the return value, TRUE/FALSE)
Here,
what you want to look up: It is called the LOOKUP value. This is the value that you want to search for in any range. You can manually type it or select any cell. Typing manually if the value is a number then you can just type the number but if it’s a text then you need to insert parentheses “”.
where you want to look for it: In this part, you declare where to look for the LOOKUP value. It is a range that should start with the LOOKUP value which means the first column of the range should have the LOOKUP value.
column number of the return value: Here you declare which value to return. To be specific which column, that has the return value of the range.
TRUE/FALSE: This declares if the function will match the LOOKUP value to exact or approximate. 0 or FALSE refers to the Exact match and 1 or TRUE refers to the Approximate match.
📕 Read More: 8 Ways to VLOOKUP to Return Multiple Values Vertically in Excel
Learn to Fix When VLOOKUP Returns #N/A If Match Exists in Excel Analyzing These 6 Reasons
Here in this article, we will learn reasons and solutions where VLOOKUP Returns #N/A When Match Exists in Excel using different approaches. To be exact, I’ve provided a total of 7 methods down below.
1. Function Can’t Find Exact Match
Here, we have the dataset for students and their submission date of an assignment. Here we are learning how to use conditional formatting for dates overdue setting a basic condition. For instance, here we want to know the cells which are past the deadline follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- In order to lookup a certain value corresponding to another value we use the VLOOKUP Here we are searching the WC 2022 Goals value of Mbappe in the Player column by the following formula.
=VLOOKUP(C18,C6:E16,3,0)
- The results will be like this #N/A an error. Here this shows because the value we are looking for, Mbappe is not present in the range of lookup.
- The solution is we need to have Mbappe on the list or search for other players that are on the list. Let’s change the Player cell to Messi and as you can see that the WC 2022 Goals changed to 7.
📕 Read More: 7 Ways to Use VLOOKUP to Compare Two Lists
2. The Lookup Value Provided is Not Present in Table Array Argument’s First Column
While using the VLOOKUP function we have to state what you want to look up and where you want to look for it. We need to keep the LOOKUP value in the first column of what you want to look up range if not Excel will return a #N/A error.
⬇️⬇️ STEPS ⬇️⬇️
- Here, we are looking up the value of cell C18 in the range B6:E16 and returning the value from the 3rd column.
- As you can see here, the output says returns a #N/A Because the lookup value Messi is not in the first column of the range B6:E16.
- We can correct the error by reannouncing the range to C6:E16 where the lookup value is in the first column.
- After that Press Enter to see the difference.
📕 Read More: What Is Table Array in VLOOKUP in Excel?
3. The Numbers are Deployed as Text
If the numbers are in text format the VLOOKUP function will return an error. We have to match the format while looking up a value. This is because the function looks for a number value and stumbles upon a text value instead. Make sure that is not the case.
Follow these steps for more details.
⬇️⬇️ STEPS ⬇️⬇️
- Here, we are searching for the club whose player has scored 7 goals. The formula is down below.
=VLOOKUP(C18,D6:E16,2,0)
- As you can see, the cells contain errors because the values are in text format and that’s why the result is as in the following picture.
- Thus we will see the following result.
4. Large Floating Point Value
Due to the floating point accuracy, Excel returns the #N/A error when you enter time data or huge decimal numbers in cells. After a decimal point, there are numbers known as floating point numbers. (Time values are kept in Excel as floating-point figures) For the function to function properly, the floating point numbers must be rounded to 5 significant digits because Excel cannot hold numbers with very big floating points.
While not everyone encounters this error working especially in some newer versions of Excel, it is still worth checking up on the value.
📕 Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet
5. The Lookup Value is Less Than Array’s Smallest Value
The lookup value must be greater than the lowest value in the array if the range lookup parameter is set to TRUE or 1; else, #N/A will be shown. 1 or TRUE delivers the closest or approximate value that is smaller than the lookup value when it searches the array for a roughly matching value. So if we are searching for Approximate values we need to have the lookup value in between the range if the lookup value is smaller than the array’s smallest value the function will return an error.
This is kind of similar to the one we discussed in the first solution. Either way, make sure the value exists as it is in the original dataset.
📕 Read More: 2 Ways to Use VLOOKUP Function with Two Lookup Values
6. Table Lookup Values with Extra Spaces
If the Lookup value has extra space except for the value both in front or at last Excel returns an error because Excel stores the space as a value. Having extra spaces before the actual value messes up the string. Excel recognizes them as two different values and so the function do not pick them up as an exact match while searching.
Of course, there are different methods you can use to get rid of the spaces from existing values. However, make sure to remove them for the lookup functions to work properly. Here is a detailed demonstration of that.
⬇️⬇️ STEPS ⬇️⬇️
- Here, we started the lookup value with two spaces and thus the result returns the error.
- As we change the lookup value by clearing the spaces, the results are properly shown.
📕 Read More: 3 Quick Ways to Vlookup and Return Multiple Values Horizontally
What to Do While Encountering Syntax Error in VLOOKUP?
In order to use the VLOOKUP function properly, we need to maintain the syntax of it, else the function returns an error. We need to maintain the sequence of every function’s arguments. We can recall the syntax of any function by typing it with a “=” before it, and Excel displays a popup regarding the syntax.
Let’s look at this example for a fact.
As you can see the syntax in the picture above is wrong. We have inserted the LOOKUP value later so the function returns as the following picture.
Now as we correct the syntax replacing the arguments in their respective positions follow the pictures below.
The results are delivered properly now as the syntax is correct.
📕 Read More: Excel Formula: Use VLOOKUP Function with Multiple Sheets
📄 Important Notes
🖊️ Use shortcuts to reduce the time for instance F4 for absolute value
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️ Be careful while defining arrays, to be accurate follow syntax.
📝 Takeaways from This Article
📌 The article demonstrated VLOOKUP function to inquire about #N/A errors in Excel.
📌 In the first section, we showed how #N/A error occurs and how to solve it in excel
📌 Then, we demonstrated different cases like LOOKUP value lower than the Lowest value in array, LOOKUP value with extra space, Numbers deployed as texts, Large floating Points, no matching LOOKUP value in the array and lookup value is not in the array’s first column which follow the same error and their solution in Excel.
📌 Finally, we properly described the function and its syntax.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this VLOOKUP Returns #n/a When Match Exists lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
Related Articles
- VLOOKUP to Find Duplicates in Two Columns in Excel
- Find MAX of Multiple Values Using VLOOKUP Function in Excel
- 5 Examples to Use IF and VLOOKUP as Nested Function in Excel
