Getting rid of the #N/A in a vlookup

[ibabs]

hello.
I am working on learning vlookups..
I have enclosed a snapshot of what i am trying to get rid of..
The vlookups work fine, but when I auto-filled to have the rest fill in (like a list of products and their costs), where there is no value yet, you get that #N/A symbol. And, because of that symbol, I cannot get a running tally for the total cost..that also comes up #N/A.

Is there a way to not have this(#N/A) show, when there is no value yet in that row to fill in the lookups, and also have a total work with that column that now does have that showing??

I hope that makes sense, but I think the snapshot shows it best.

thanks
babs
Picture 6.jpg

[shg]

=if(isna(your lookup), 0, your lookup)

[NBVC]

Try

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(....))

where you would insert your Vlookup arguments as appropriate..

This will return blank (null) if no match found

[ibabs]

hi,

I am not sure I understand what that means?

HEre is a snapshot of the entire thing...



I have a defined named area called "products"

For the lookup area, I am using the product number, to bring in the part name, description and price. I am manually putting in the quantity so that it can multiply the quantity times the price. Here is what one lookup looks like:

=VLOOKUP(F19,products,2)

I tried to put your info in my lookups, but they keep giving me errors, so I am not doing it right.

thanks!
babs

[NBVC]

Try

=IF(ISNA(VLOOKUP(F19,products,2)),"",VLOOKUP(F19,products,2))

FYI... it is always better to post an actual sample workbook so we can see the formula at work.. maybe there are other issues that don't make it work....

[ibabs]

hello...
OK...almost there ;-)
Here is the file....as per your suggestion..

Your code worked fine for the lookup....now, where I have the amount times the quantity, I get the #Value error, so again, I cannot get a total....

any thoughts on that one ;-)
thanks!
babs

[NBVC]

Replace the formula in K19 with:

=IF(J19="","",I19*J19)

copieed down

[ibabs]

Perfect...
I see, I need some sort of if statement that is blank, unless there really is a value..
Got it!!
Thanks so much..makes perfect sense ;-)
babs