Excel VLOOKUP function is used to lookout and retrieve the information from a data table. However, sometimes you may find that even if you have done everything right, VLOOKUP does not work. There are a few common problems due to which it will return #N/A error. I am listing down the common problems and their troubleshooting. Try the following solutions and I am sure, it should start returning you the correct value.
Formula not correct
Believe it or not, in most of the cases, the main problem is the incorrect formula. A simple mistake can prevent the formula to fetch the correct result. So, make sure that you have written the correct syntax – see the comma, spelling mistakes, brackets, extra spaces, etc. first.
Just make sure there is no additional space, you have used comma at appropriate place and most importantly not missed the last optional range_lookup value. In most of the cases you will pass the value “FALSE” for it. Another thing to keep in mind is that the lookup_value must be in the first column of table_array. Use insert function dialog box instead of writing to code manually to avoid typing mistakes.
Use of Relative Reference
In practical scenario, most of the times you will write the formula in one cell only and then drag it to the other cells. If you are not using absolute reference, the table_array will get changed along with lookup value as you drag it. But for most practical purposes, your table_array should be same. This will lead to getting #N/A error in many of your cells. Let’ understand it with this example.
Here, we want to get the cost of Pant, Belt and T-Shirt from the table_array (A2:B7). For cell E2, we write the following VLOOKUP Formula.=VLOOKUP(D2,A2:B7,2,FALSE)
Now, for cells E3 and E4 where we want to get the cost of Belt and T-Shirt, we simply drag to copy the formula. But as you can see, we are getting the “#N/A” error in cell E4.
This is because, once you drag to copy the formula, the table_array also gets changed. Look at the formula in E4 cell.
It has changed from A2:B7 to A4:B9
To overcome this issue, you need to use the absolute reference. For doing so, select the table_array (A2:B7) in cell E2 and press F4. You will find “$” before A, 2, B and 9. It means that the range is now fixed and will not change when you drag the formula.
Press F4 again and the “$” before A and B will disappear. It means, A and B (columns) will change but 2 and 9 (rows) will always be constant while you drag. If you press F4 once again, the $ sign before 2 and 9 will disappear and appear again for A and B. Now the rows can change but the columns are constant. Do some experiments and lookout in the formula after dragging to understand it once for all.
Format Change – Numbers stored as Text
This is another common problem. Many times, the lookup value which is a number is stored as text in the table_array. VLOOKUP is format sensitive, so you will get #N/A error.
See the following example:
Make sure that the format of lookup value and the value in the first column of table_array is same. To change the format of number stored as text to numbers, select all the affected cells, click on the warning icon before it and select convert to number. However, if you don’t want to change anything in your table_array, you can just modify the formula a little.
Here we are first converting the number in D2 cell to text to match it with the table_array. Now since both the formats are same, you don’t get any error.
Lookup Value is not in the Left most column
As I mentioned it earlier, the lookup value must be in the first (left most) column of your table_array. If it is not the case, you may use INDEX-MATCH functions together. This is a good VLOOKUP alternative and you can go through my INDEX MATCH Functions tutorial.
Spaces before or after lookup value
There may be some spaces before or after lookup value. Solution is very simple – use TRIM() function to remove the extra spaces from both lookup value and first column of table array. How do you check the spaces? Select the cell with value and hit F2 button to inspect the value in formula bar.
Removing #N/A Error
In some cases you get genuine #N/A (value not available) errors. If you don’t want this error to appear and simply want a blank column instead, you can use the following trick to remove this.
=IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=TRUE), “”, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))
These are some of the common reasons of Excel VLOOKUP formula not working. So, next time when you get stuck, check the above troubleshooting first. Even after the above solutions, if it does not work, let me know in the comments below- we will try to come out from it together.