Forum Discussion
#REF! and #N/A Error while using Vlookup to fetch value
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?
4 Replies
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.
Thank you so much, now I get the exact values as I required.
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.
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.
