How to correct #NA error in vlookup function – MS Excel

This article aims to provide solution for one of the most common problems with vlookup. Vlookup does not work properly when source and destination data both occur in the same workbook or worksheet. This means, for vlookup to work properly, you should use two different workbooks (excel files) for  source and destination. Let’s see why.

Download sample vlookup file

Following example illustrates #NA error due to the above mentioned issue (refer rows 7 and 9)

The problem here is that the selected range of source cells keep changing when you drag the formula down (while trying to copy it to other cells in the column). Check the following image which illustrates that the range will be different for each cell in column D.

To overcome this, you just need to make the range to a constant. It can be done by adding $ sign before and after the cell reference letter (here B and D in this example).

You can also download the excel sheet for your reference by clicking here. If you could not find a solution to the problem, we recommend that you go through the following websites for a solution.

External links

How to correct a #N/A error in the VLOOKUP function

Excel VLOOKUP not working – solutions for N/A, NAME and VALUE errors

Remove #N/A Excel Error – Replace #N/A in VLOOKUP with Valid Value