VLOOKUP reference error

[gvb]

Hi all. This is probably very simple to all you experts !

I have a spreadsheet that returns a value from a VLOOKUP. However, sometimes this will reture a #REF error because there is no value. How can i get it to show 0 if there is no value, in order for the SUM calculation to not error.

THanks,

Greg

[NBVC]

Are you sure it is the REF# error and not the #N/A error?

If it is the REF error, then something else is wrong, like referencing outside the range....

If it is #N/A, then something like

=If(isna(Vlookup(...)),0,Vlookup(....))

[gvb]

You are correct. It is referencing outside the range because there is no value, which in turn creates no line in the pivot table. The problem is because my column references from three different pivot tables, two of which have the value and therefore have the specific line in the table. The one pivot table doesnt have the value, and thus references outside the range.

Hope that is clearer and feel free to suggest other ideas instead of returning a 0 value if there is a #REF.

Thanks for the quick reply.

greg

[NBVC]

Replace Isna() with IsError()

[gvb]

Almost there. When i use that code is returns FALSE. Is there anyway to make it return the number 0 or leave it blank. This is going to be on a report, and a big FALSE will just stick out.

Thanks
greg

[NBVC]

Make sure your brackets are all there and in the correct places...

if the vlookup returns an error, this should return a 0....

=If(iserror(Vlookup(...)),0,Vlookup(....))

[gvb]

Perfect.

Thanks a bunch again.

greg

[gvb]

How do i mark it solved

[NBVC]

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[gvb]

yeah i saw your signature.