Excel spreadsheet applications and tips

Home

 

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 together with external links pointing to other issues. Vlookup does not work correctly if both the source and destination data both occur in the same workbook or within the same worksheet. That means for vlookup to work properly you should use two different workbooks (or excel files) as the source and destination. Let's find out why.

Download sample vlookup file

The following example illustrates the #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 (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