VLOOKUP returning #N/A
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 |
VLOOKUP returning #N/A
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 |
VLOOKUP returning #N/A
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(...)) |
VLOOKUP returning #N/A
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 |
VLOOKUP returning #N/A
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 |
All times are GMT +1. The time now is 09:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com