Excel Vlookup HELP

[links_now]

I have a drop down menu that I use for parts and pricing. The problem I have
is on my quote sheet it won't let me add up all of my pricing because not all
fields are filled in some are blank and when they are blank the total is #N/A
not allowing you to add up #N/A mixed with numbers or it will come up #N/A.

=VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)

Is there a way an emty cell can be equal to zero without having to add and
select zero from a drop down menu giving the zero a $.00 cost.
I hope you can understand what i am doing. Thanks

 

[Ashley1432]

Use this=

IF(ISERROR(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)),0,(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)))

That way if it would normally have an error it will convert it to 0.


Ashley

 

[T. Valko]

You can use something like this that will ignore the errors:

=SUMIF(A1:A10,"<1E100")

However, you will probably be better off preventing those errors in the
first place:

=IF(ISNA(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,0)),0,VLOOKUP(SHELVING!J22,Cost!A1:B16,2,0))

 

[Gord Dibben]

Ashley

ISERROR will mask all errors.

Better to trap for NA only

See Biff's post for ISNA function


Gord Dibben MS Excel MVP