VLOOKUP returning #N/A

[Nicky]

My VLOOKUP is returning #N/A because the cell which is the lookup value isn't
in the table array its looking it up in. I am copying this data over from
another source . Is there a way to return either 0 or "" . As soon as I put
the lookup reference in the data table it works fine. I have tryed ISBLANK
and ISNA but doesn't seem to work.
Below is what I am currently using.

=IF(ISBLANK(A5),"",VLOOKUP(A5,GOODSIN,3,FALSE))

many thanks...Nicky

 

[JE McGimpsey]

One way:

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

 

[new1]

My VLOOKUP is returning #N/A because the cell which is the lookup value isn't
in the table array its looking it up in. I am copying this data over from
another source . Is there a way to return either 0 or "" . As soon as I put
the lookup reference in the data table it works fine. I have tryed ISBLANK
and ISNA but doesn't seem to work.  
Below is what I am currently using.

=IF(ISBLANK(A5),"",VLOOKUP(A5,GOODSIN,3,FALSE))

many thanks...Nicky

There's also IF(ISERROR(VLOOKUP(...));"";VLOOKUP(...))


new1@[no/spam]realce.net

 

[JE McGimpsey]

Just note that ISERROR() will also trap 'real' errors that would
indicate a problem in the worksheet.

For instance, if a column were deleted and a #REF! error occured, using
IF(ISERROR(),,) would happily ignore that, and return "".

Using ISNA() is more specific and less prone to invalidate your model.

 

[Nicky]

Thanks very much.. point taken on ISERROR so have used ISNA.

 

[Keith Faulconer]

One thing that I do is to put the lookup formula in a column you are not
using, say Z1 for this example, then in the table you are using enter this
formula:

=if(iserror(z1),0,z1)

This way, if there is an error on the lookup you get 0 and if there is no
error, you get the lookup value.