Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning #N/A
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(...)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(...)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP always returning the same value! | Excel Discussion (Misc queries) | |||
VLOOKUP Keeps Returning #N/A | Excel Worksheet Functions | |||
VLOOKUP returning #N/A | Excel Worksheet Functions | |||
vlookup returning #NA | Excel Discussion (Misc queries) | |||
Vlookup and returning #n/a | Excel Worksheet Functions |