Greetings Evreryone,
How can you write a VLOOKUP formula to prevent it from giving you an #N/A error message when it is unable to find the lookup value.
For example: If i am looking for let say number 101 in column 'A" but there is no number one, how do i get the vlookup formula to return Zero (0) instead of #N/A.
Last edited by headley4ever; 12-07-2010 at 06:25 PM.
Test for the existence of the value before you use VLOOKUP.
=IF(COUNTIF(B1:B10,A1)>0,VLOOKUP(A1,B1:B10,0),"")
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
Or:
=IF(ISNA(your formula),0,your formula)
The formula i am using is =Vlookup(101,A1:C200,3,0) NB: 101 is the Item number for a Product we sell and this is in column "A" along with 200 other item numbers for other products we sell. This formula gives us the amount sold for the respective Products based on their lookup value which is the item numbers. The problem i am having is that if an item is not sold the item number for that product would not appear on the spreadsheet so i will get #N/A in the cells for any item that is not sold. I Need know how i can get VLOOKUP to return Zero (0) in the cells of these items that is not sold instead of #N/A
By using what has been given.
=IF(ISNA(Vlookup(101,A1:C200,3,0)),0,Vlookup(101,A1:C200,3,0))
If you prefer the other method that was suggested adapt it thusly:
=IF(COUNTIF(A1:A200,A1)>0,VLOOKUP(A1,A1:C200,3,0),0)
or
=IF(COUNTIF(A1:A200,A1)=0,"",VLOOKUP(A1,A1:C200,3,0))
NOTE: A1 here would be the cell holding your 101 value
Last edited by Cutter; 12-07-2010 at 06:31 PM.
Thanks ........ This definetly works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks