VLOOKUP Error (Table of Contents)
Introduction to VLOOKUP Error
We all know how to apply and application of Vlookup function. And we have seen the various type of ways to apply Vlookup function in previous articles. We all also somewhere and sometimes have seen the different error message which could and couldn’t be able to resolve it. Vlookup Error are the error which may happen with different reasons. The basic and most common reasons are incorrect range, no match, special characters in the lookup range as well in lookup cell. In this article, we will see the different type of Vlookup Error and logic behind those errors as well.
How to Use VLOOKUP Error?
Below are the most commonly occurred Vlookup Errors.
- #N/A Error
- #Value! Error
- #Name? Error
- #REF! Error
MS Excel has standard way to prompt the errors it is happening into the cells while applying any of the Excel function, is by putting HASH sign (“#”) before that. We all have seen this, we rarely have noticed. Above mentioned errors are some of them. Every error has different meaning and reason to be occurred which we will be seeing in upcoming examples.
Examples of VLOOKUP Error
Lets us discuss the examples of VLOOKUP Error.
Example #1
In this example, we will see the first and most common type of error, #N/A. For this, we have some sample data as shown below. As we can see, we have 2 tables. First table has the Sales data for some electronics products like Laptop, Desktop, Notebook, and Tablet, and second table is that table where we will be looking up the value from first table. We have created a common key by combining OWNER and PRODUCT columns, separated by underscore (“_”).
Let’s apply the Vlookup in the cell I3. Apply the vlookup using the range and lookup cell as shown below. As we can see below, Lookup cell is F3 which is our Key of Table2, lookup range from selected from Table1.
Once we drag the applied function, we will see that for cell I5, we got an error as #N/A.
To find the reason behind this, there are 2 possibilities. We either may have some space or special character in lookup cell F4 or first column of Lookup range. To find this, we can check the length of both the cell of same row using LEN formula. Below is row 13 we have used LEN function by selecting cell of row 5.
After using LEN Formula in cell A13, the output is shown below:
LEN Formula use in cell F13.
After using the above formula, the output is shown below:
If we Clear or Delete that extra space or special character from the cell A5, we will be getting Quantity sold for Ben_Notebook for table2 as shown below.
Example #2
In this example, we will see the Vlookup error #VALUE! Which happens due to incorrect syntax or missing any of the syntax value while applying Vlookup. We will be using the same data which we have seen in example-1. Let’s apply the vlookup at cell I3 and not to choose the any of the syntax value, let say it be Col_Index_num as shown below.
And if press enter to exit from the syntax, we will see that cell I3 will have error as #VALUE!.
To rectify this, let’s choose the missing Column Index Number in the syntax as 4 and Enter.
We will see that now the values are looked up properly.
Example #3
In this example, we will see the Vlookup error type #REF!, which happens due to selection incorrect Column index number, exceeding the limit beyond the selection. In cell I3, we have applied Vlookup function choosing everything as we have seen in above example, but the column index number is now selected as 5 as circled below.
Once press Enter key to exit from the syntax, we will get the error message as #REF!.
Now if we correct the column index number from 5th to 4th position, then we would get the right value looked up.
Once we exit, we will notice the value there.
Example #4
Vlookup error #NAME? we get when we select all right position and cells in syntax but mistakenly typed the wrong name of the syntax. As per my experience, I mostly end up typing function CLEAN instead of VLOOKUP. We will use the same data and select the different function with the syntax of Vlookup. Here intentionally chose BLOOKUP instead of VLOOKUP with the same syntax.
And once exit from the syntax, we will get #NAME? error as shown below.
To rectify this kind of error, we must select the right function with right syntax. As we have selected BLOOKUP instead of VLOOKUP.
Pros of Vlookup Error
- It is very important to know the different types of error which are available in excel for Vlookup function.
- Vlookup Errors are not limited to the above-shown variations.
Things to Remember
- #N/A error occurs due to absence of lookup value.
- #NAME? error occurs due to incorrect selection of function but keeping the right syntax.
- #REF! error occurs due to incorrect selection of column index number.
- #Value! Error occurs when we miss the any of the syntax sequence.
- We must know all possible types of errors which happens in VLOOKUP function so that we would be able to rectify it whenever it is required.
- We can calculate the formula using Evaluate Formula option available Formulas menu ribbon under Formula Auditing section.
Recommended Articles
This has been a guide to VLOOKUP Error. Here we discuss How to Use VLOOKUP Error along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
