#REF! and #N/A Error while using Vlookup to fetch value

Occasional Contributor


Facing Ref error while fetching  value. Source sheet is AR-216 and Main sheet where I want the value to go in is Sheet1.

1. I am able to get the rows with #ref! error but not with exact value.

2.The rest cells column should be blank and not #N/A. 

3. Also, can I fetch data from another document and not a different sheet?

4 Replies


Try it this way:


The IFERROR(......,"") part makes sure that when a code is not found the cell is left blank.

And note that the #REF! errors were caused by the fact that the VLOOKUP functions was looking for the 3rd column in a range that only has two. 


In the formula =VLOOKUP(B50,'AR-216'!$D$6:$E$16,3,0), the lookup range D6:E16 has only 2 columns, but you refer to the 3rd column.

You should either expand the range to include more columns, or change the third argument from 3 to 2, depending on which information you want to return.


You can refer to another workbook:


=VLOOKUP(B50,'[Consumption 2020 April - December.xlsx]AR-216'!$D$6:$E$16,2,0)


Create this formula while the other workbook is open. Then close that other workbook. Excel will automatically add the path of the workbook.

@Hans Vogelaar 

Thank you so much, now I get the exact values as I required.


Thank you so much, all my problems are solved. I guess this would change my days work in a few hours. You people are great.