5 Common Excel VLOOKUP Errors and How to Avoid Them
Unsplash image - no attribution required - https://unsplash.com/photos/GauA0hiEwDk
The VLOOKUP function in Excel is a great tool to save you time and reduce manual effort when dealing with substantial amounts of data. However, it can cause frustration when you first start out and cannot seem to get it to work.
Having a challenging time getting the output you want? Are you wondering why the VLOOKUP function is returning #N/A instead of the value you need? This article will help you identify common errors and how you’re able to correct them.
1. Lookup Value in the Wrong Column
One of the most common errors you will see whenusing the VLOOKUP formulais having the function return only#N/Avalues. This error happens when it cannot find the lookup value you asked it to search for.
In some cases, you canuse the XLOOKUP functioninstead, however it is just as easy to fix the error in your VLOOKUP equation.
How to Resolve This Error:
The reason VLOOKUP is not finding the desired value could be due to the value not being in the table. However, if it is returning all#N/Avalues, and the lookup value is text, chances are, it is because it can only read left to right.
Whencreating tables in Excelfor VLOOKUP, make sure to put the lookup value to the left of the value you want to return. The easiest way to do this is to make it the first column in the table.
Now, your formula should return a value:
2. Incorrect Number Formatting
When dealing with numbers as your lookup value, it can be very frustrating trying to figure out what is going wrong. If you are getting an error, the first thing to check is your number formatting.
Issues with formatting can occur when importing data from external databases or copying the data from an external source. To tell if your data is reading as text, check if it is aligned to the left or has an error symbol. Numbers, when formatted correctly, show aligned to the right.
If the numbers in your table are showing as text, you will need to convert them to numbers. The most efficient way to do so is by using theError Indicator. Using the Error Indicator to format numbers:
3. Wrong Value Returned Due to Similar Values
In some cases, you may see a value returned however, the lookup value does not exist. This can happen if you do not set up your formula to look for exact matches only.
Easily solve this problem, by updating your formula, with these steps:
4. Not Locking the Data Range
So far, we have used single VLOOKUP values. However, the VLOOKUP function is also great for automatically pulling information from one table into another.
For example, if you have a table showing sales by employee ID and another showing employee names and ID numbers, you’re able to use this tool to show sales by employee name by looking up the ID numbers.
When using a VLOOKUP formula in a column, rather than a single cell, the data range may change and lead to incorrect values or errors being returned.
To prevent this from happening, make sure to lock your data range, ensuring the formula uses the same lookup range for every cell.
5. SUM Formula Not Working Due to #N/A Values
Even when your formula is working correctly,#NAvalues may still appear if the value does not exist in the table. This is quite common and can interfere with other formulas applied to your column.
One of the most used functions is theSUM formula,which cannot calculate if there are non-numerical values in the range you want to add together.
Luckily, there are two easy ways to fix this problem; using theSUMIFformula or by incorporating theIFERRORfunction into yourVLOOKUPequation.
Using SUMIF instead of the SUM formula:
Using the IFERROR function in front of the VLOOKUP formula:
Master the VLOOKUP Formula in Excel by Avoiding These Common Errors
VLOOKUP is a powerful formula when used correctly, so using these tips to avoid mistakes will make a difference. With these tips, you will save time and increase your productivity.
From formatting and altering ranges, to using similar formulas like XLOOKUP, there is always a way to increase productivity. The best way is to keep learning and continuously improving your skills.
Excel’s XLOOKUP and VLOOKUP functions find a value in a table or a list and return related results. Here’s how they differ and which one works better.
Taming data is easier than it looks.
These films will leave you questioning humanity, but also wanting more.
Lose your laptop without this feature, and you’ll wish you had turned it on.
Obsidian finally feels complete.
Revolutionize your driving experience with these game-changing CarPlay additions.
