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

[Somersetinventory]

Hey,

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?

[#2]

@Somersetinventory 

Try it this way:

=IFERROR(VLOOKUP(B3,'AR-216'!$D$6:$E$16,2,0),"")

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. 

[#3]

@Somersetinventory 

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.

[#4]

@Hans Vogelaar 

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

[#5]

@Riny_van_Eekelen 

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.