The purpose of VLOOKUP is to search for and return data from another column in your Excel spreadsheet. Unfortunately, if you get your VLOOKUP formula wrong, Excel will throw an error at you. Let’s go through some common VLOOKUP errors and explain how to troubleshoot them.
Limitations of VLOOKUP
Before you begin using VLOOKUP, you should be aware that it isn’t always the best option for Excel users. To begin with, it can’t be used to lookup data to the left of it. It’ll also only display the first value it finds, meaning that VLOOKUP isn’t an option for data ranges with duplicated values. Your search column also needs to be the furthest left column in your data range. In the example below, the furthest column (column A) is used as the search column. There are no duplicated values in the range, and the lookup data (in this case, data from column B) is to the right of the search column.
The INDEX and MATCH functions would be good alternatives if any of these issues are a problem, as would the new XLOOKUP function in Excel, currently in beta testing. VLOOKUP also requires data to be arranged in rows to be able to accurately search and return data. HLOOKUP would be a good alternative if this isn’t the case. There are some additional limitations to VLOOKUP formulae that can cause errors, as we’ll explain further.
VLOOKUP and #N/A Errors
One of the most common VLOOKUP errors in Excel is the #N/A error. This error occurs when VLOOKUP can’t find the value you’re searching for. To begin with, the search value might not exist in your data range, or you may have used the wrong value. If you see an N/A error, double-check the value in your VLOOKUP formula. If the value is correct, then your search value doesn’t exist. This assumes you’re using VLOOKUP to find exact matches, with the range_lookup argument set to FALSE.
In the above example, searching for a Student ID with the number 104 (in cell G4) returns an #N/A error because the minimum ID number in the range is 105. If the range_lookup argument at the end of your VLOOKUP formula is missing or set to TRUE, then VLOOKUP will return an #N/A error if your data range isn’t sorted in ascending order. It will also return an #N/A error if your search value is smaller than the lowest value in the range.
In the above example, the Student ID values are mixed up. Despite a value of 105 existing in the range, VLOOKUP can’t run a correct search with the range_lookup argument set to TRUE because column A isn’t sorted in ascending order. Other common reasons for #N/A errors include using a search column that isn’t the furthest left and using cell references for search values that contain numbers but are formatted as text, or contain surplus characters like spaces.
Troubleshooting #VALUE Errors
The #VALUE error is usually a sign that the formula containing the VLOOKUP function is incorrect in some way. In most cases, this is usually because of the cell you’re referencing as your search value. The maximum size of a VLOOKUP lookup value is 255 characters. If you’re dealing with cells that contain longer character strings, VLOOKUP won’t be able to handle them.
The only workaround for this is to replace your VLOOKUP formula with a combined INDEX and MATCH formula. For example, where one column contains a cell with a string of more than 255 characters, a nested MATCH function inside of INDEX can be used to locate this data instead. In the example below, INDEX returns the value in cell B4, using the range in column A to identify the correct row. This uses the nested MATCH function to identify the string in column A (containing 300 characters) that matches cell H4. In this case, that is cell A4, with INDEX returning 108 (the value of B4).
This error will also appear if you’ve used an incorrect reference to cells in your formula, especially if you’re using a data range from another workbook. Workbook references need to be enclosed in square brackets for the formula to work correctly.
If you encounter a #VALUE error, double-check your VLOOKUP formula to confirm that your cell references are correct.
#NAME and VLOOKUP
If your VLOOKUP error isn’t a #VALUE error or an #N/A error, then it’s probably a #NAME error. Before you panic at the thought of this one, rest assured—it’s the easiest VLOOKUP error to fix.
A #NAME error appears when you’ve misspelled a function in Excel, whether it’s VLOOKUP or another function like SUM. Click on your VLOOKUP cell and double-check that you’ve actually spelled VLOOKUP correctly. If there are no other issues, your VLOOKUP formula will work once this error is corrected.
Using Other Excel Functions
It’s a bold statement, but functions like VLOOKUP will change your life. At the very least, it’ll change your working life, making Excel a more powerful tool for data analysis. If VLOOKUP isn’t right for you, then take advantage of these top Excel functions instead.
