Excel spreadsheet applications and tips
How to correct #NA error in vlookup function - MS Excel?
Let's start with the most common problem faced with vloopup. Probably you have come to this site because you tried to operate vlookup function with both the source and destination data in the same workbook or within the same worksheet. That means you would not have faced this issue if you used two different workbooks (or excel files) as the source and destination. Lets find out why.
The following example illustrates the #NA error due to the above mentioned issue (refer rows 7 and 9)
The problem here is with the selected range. The range keeps on changing when you drag the formula down to copy it to other cells in the column (refer image below). That means, in this example 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.