In this article, we are going to explain common Vlookup errors and how to correct them with easy adjustments.
When it comes to lookup for values in excel, Vlookup is everybody’s go to function. While it is easy to use, you can easily get errors due to wrong data types or function configuration. Here are some of common vlookup errors:
Forgetting to fix ranges:
If you are not using full columns as ranges, you need to fix your table array with $ signs. If you don’t fix your table array, it will move as you copy your vlookup function to other cells and will not return the results you expect. Most often, it will return a #N/A error due to not finding what you are looking for.
An example vlookup function with fixed range:
If you copy this formula to other cells, it will still search values from A4:B35 range in Sheet2. Therefore, you will get correct values for your lookup. If your formula returns #N/A, it is a correct error this time. Not due to your table array is shifted to another cell range.
Numbers formatted as text:
Numbers may seem like numbers on a sheet but if they are formatted as text, Excel will not recognize them as numbers. Therefore, if you use numbers like ID code, etc. as a lookup value, you need to make sure both your lookup values and values in table array are numbers or text. If one is text and other is numbers, you will get a #N/A error.
To overcome this error, you can convert values formatted as text to numbers and make both lookup value and table array values numbers. This way you function will work as intended.
Here is an alternative way without reformatting: search-number-values-in-texts-and-text-in-number-values
Notes for Vlookup lovers:
These are not vlookup errors but thinks to keep in mind when relying on vlookup:
- Vlookup always returns first match in a column of values if there are more than one match.
- You shouldn’t omit last part of vlookup function (range lookup). Input 0 always if otherwise is needed or your function may return incorrect values.
- Vlookup always searches from left to right. If your columns are not ordered from left to right, you can’t use vlookup. You need to rearrange columns or you may use INDEX+MATCH