Since the VLOOKUP function is one of the advanced lookup functions, it can take a while to completely get the hang of its functionality. During the application, most beginners tend to struggle with errors like #N/A when using the formula.
Apart from errors, you may also experience issues like VLOOKUP not working across sheets, resulting in the incorrect formula, formula not copying down, showing the formula instead of output, etc. So, let’s learn all the causes and proven fixes to solve the VLOOKUP not working.
Solve VLOOKUP Errors
#N/A Error: This is the most common error users experience when using the VLOOKUP function. It is mainly because there are a number of reasons that causes the #N/A error in the formula.
The VLOOKUP formula will result in a #N/A cell error message when there is no lookup value in the table range. Especially, when your range lookup is a FALSE argument that searches for an exact match. Besides, you’ll also get this error if the formula is prompted to look to the right column. Or, when the Column Index is not correct.
To address this error, try out these troubleshooting steps.
- Firstly, check and ensure there is a Lookup value in the table range.
- See if there are any Typos in the Lookup value.
- Make sure your lookup value is in the First column of the table range.
- Ensure the Column Index to return the value is correct.
- Use TRUE as a range argument to return the closest match. But, make sure to sort the data first.
- Delete unwanted spaces or non-printing characters.
#NAME? Error: If you get #NAME? Error in the VLOOKUP function, it’s possible you haven’t properly entered the text arguments. By proper, I mean there could be a text typo or no use of double quotation marks.
To solve the error, all you need to do is insert the Text arguments within the double-quotation mark.
For typos, check and enter the correct spelling.
#VALUE? Error: This error occurs when you’ve entered the column index number 0 or less than 0. Your minimum column index number to return the value should be 1. To solve this, look at the col_index_num and make sure the value is 1 or more.
#REF! Error: There are two instances when the formula gives you #REF! Error. The main cause for the error is when you’ve deleted a column of a lookup table, especially the one to return the value from. Next is when the column index is greater than the lookup table.
To fix #REF! Error, enter the accurate argument in the col_index_num.
#SPILL! Error: Have you passed down an entire column range in the formula? In the VLOOKUP function, #SPILL! error means the formula cannot return the entire array output as there’s no space in adjacent cells. VLOOKUP does not return an array like the XLOOKUP function.
#FIELD! Error: If you see a #FIELD! Error, there’s a mistake in your formula. You may have entered (.) to separate arguments in place of (.) commas. Check and correct them.
How to Fix VLOOKUP Not Working?
We’ve discussed several types of errors you may get when using the VLOOKUP function and the fixes to solve them. But, if VLOOKUP is not working without any errors, there are different causes and fixes for them.
Before starting, quick Restart the Excel app. Since VLOOKUP is powerful, it can cause Excel to lag or glitch sometimes. Especially, when you’re using the formula across sheets and workbooks.
Check Arguments
When Excel detects even the slightest mistake in the formula arguments, you’ll get an error specific to the blunder. However, if you’re getting incorrect results and not the error, maybe you’ve entered the correct arguments. But, the referencing might not be correct.
Are you sure you’ve passed down the lookup value, table, column index, and boolean as intended to return that value? Recheck all of the arguments and the values in a lookup table.
Format Text Stored Value into Numbers
When your values are stored as texts in the VLOOKUP formula, either the formula stays in the cell or you’ll get a different output.
In that case, change the texts to Numbers or respective formats. Select the cells and click on Home Tab. In the Number group, click the drop-down menu and choose a Format.
Also, make sure there aren’t any leading spaces or apostrophe (‘) signs before the formula. These symbols also convert your values into texts.
Change Table Range
If you’ve added an additional column or row in the Lookup table, the VLOOKUP function won’t detect them and update the formula. You need to manually edit the range in the formula.
Besides, if your table/range is non-contagious, you can resize the table and extend the range.
Use Proper Cell References
While using a VLOOKUP function, the user must also know how to use the appropriate cell reference in the formula. Especially, if you intend to copy down the formula to return the value for the rest of the data.
You can use the Relative cell reference as usual to return the single lookup value. But, if you enter the same to drag the flash-fill, it’ll result in a 0 or #N/A error. You can see in the picture how the cells changed and referenced empty cells.
In cases like these, you’d have to apply Absolute References. To make any cell reference an Absolute, you just need to add a $ sign before and after the column name. For Instance, $A$2.
But, there’s an easier way to change it into an Absolute reference. In the first formula cell, place your cursor in a relative cell reference and press F4. Now, you can copy down the formula.
Set Automatic Calculation Option
If the VLOOKUP function is working absolutely fine but the formula isn’t updating as intended, your calculation options might be in Manual. In this scenario, you’d have to manually recalculate the sheet every time you edit the formula to update them.
To avoid this, switch the Calculation mode to Automatic. On your sheet, click on Formulas Tab. Select Calculation Options and choose Automatic.
Update Office
It’s a very rare case when the VLOOKUP works on one workbook but not on another. But, if you’ve experienced this, it could be due to unknown bugs in the software. It’s time you update Excel with the most recent software version.
- Open Excel.
- Go to Account in the sidebar.
- Choose Update Options. Then, pick Update Now.
- Restart your PC.