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

One way:

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


In article ,
Nicky wrote:

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

On 15 nov, 23:28, Nicky wrote:
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(...))

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.


In article
,
" wrote:

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

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

"Nicky" wrote:

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

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.

"Nicky" wrote in message
...
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