Vlookup bringing back #N/A results - how can I change these to 0

[RichardOates]

I am trying to do a Vlookup to bring back sales information however some products have got no sales data so the Vlookup is bringing back an #N/A result which means that the total coloumns at the bottom don't calculate.

Is there a way to either get the Vlookup to bring back 0 values rather than #N/A or for the 'total' column at the bottom to ignore the #N/A's?

Thanks

 

[JackDanIce]

Try:

=IF(ISERROR(VLOOKUP(A1,$B$1:$D$4,2,0),0,VLOOKUP(A1,$B$1:$D$4,2,0))

 

[Michael M]

try

=IF(ISNA(vlookup(your vlookup formula),0,vlookup(your vlookup formula))

 

[JackDanIce]

Or in your total cell, try:

=SUMIF(B1:B10,"<>#N/A")

 

[davygryan]

I am trying to do a Vlookup to bring back sales information however some products have got no sales data so the Vlookup is bringing back an #N/A result which means that the total coloumns at the bottom don't calculate.

Is there a way to either get the Vlookup to bring back 0 values rather than #N/A or for the 'total' column at the bottom to ignore the #N/A's?

Thanks

Try =IF((VLOOKUP("Your Lookup Value","Your Table Array",Your Column Index","FALSE")="#N/A",0,VLOOKUP("Your Lookup Value","Your Table Array",Your Column Index","FALSE))