| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
I am using VLOOKUP and when that function doesn't find the value in the
table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
Yes it can,
=IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments])) I hope this helps, Conan Kelly "buffgirl71" wrote in message ups.com... I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
Thanks to all for your responses - but I don't seem to have it quite
right. This is my formula: =IF(=ISERROR(=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE )),0,=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) but I'm getting a message that the formula contains an error. I can't see what's wrong...I must have mismatched parens or something. If anyone can see what's wrong, I'd appreciate your letting me know. Many thanks. Conan Kelly wrote: Yes it can, =IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments])) I hope this helps, Conan Kelly "buffgirl71" wrote in message ups.com... I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a val
You have too many "=" remove them all except the first one.
"buffgirl71" wrote: Thanks to all for your responses - but I don't seem to have it quite right. This is my formula: =IF(=ISERROR(=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE )),0,=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) but I'm getting a message that the formula contains an error. I can't see what's wrong...I must have mismatched parens or something. If anyone can see what's wrong, I'd appreciate your letting me know. Many thanks. Conan Kelly wrote: Yes it can, =IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments])) I hope this helps, Conan Kelly "buffgirl71" wrote in message ups.com... I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
buffgirl,
there only needs to be one equal sign (=) in this formula. Start it with one. All the rest are not needed. =IF(ISERROR(VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) ,0,VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) I hope this helps, Conan Kelly "buffgirl71" wrote in message oups.com... Thanks to all for your responses - but I don't seem to have it quite right. This is my formula: =IF(=ISERROR(=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE )),0,=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) but I'm getting a message that the formula contains an error. I can't see what's wrong...I must have mismatched parens or something. If anyone can see what's wrong, I'd appreciate your letting me know. Many thanks. Conan Kelly wrote: Yes it can, =IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments])) I hope this helps, Conan Kelly "buffgirl71" wrote in message ups.com... I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
Thanks to all for your responses - but I don't seem to have it quite
right. This is my formula: =IF(=ISERROR(=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE )),0,=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE)) but I'm getting a message that the formula contains an error. I can't see what's wrong...I must have mismatched parens or something. If anyone can see what's wrong, I'd appreciate your letting me know. Many thanks. Conan Kelly wrote: Yes it can, =IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments])) I hope this helps, Conan Kelly "buffgirl71" wrote in message ups.com... I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value f
=IF(ISNA(your formula),0,your formula)
Replace your formula with your VLOOKUP "buffgirl71" wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a valuefrom VLOOKUP
=if(iserror(vlookup(...)),0,vlookup(...))
or =if(iserror(vlookup(...)),"",vlookup(...)) The second formula will work if your summing using =sum(). That function ignores text. But if you're using something like: =b1+C1+D1 Then the summing won't work. buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
Thanks to all for your responses - but I don't seem to have it quite
right. This is my formula: =IF(=ISERROR(=VLOOKUP(I4,FUEL!$G$2:$I$2044,2,FALSE )),0,=VLOOKUP(I4,FUEL!$G$*2:$I$2044,2,FALSE)) but I'm getting a message that the formula contains an error. I can't see what's wrong...I must have mismatched parens or something. If anyone can see what's wrong, I'd appreciate your letting me know. Many thanks. Dave Peterson wrote: =if(iserror(vlookup(...)),0,vlookup(...)) or =if(iserror(vlookup(...)),"",vlookup(...)) The second formula will work if your summing using =sum(). That function ignores text. But if you're using something like: =b1+C1+D1 Then the summing won't work. buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
HALLELUIA!!! You're all geniuses - thank you SO much - it works
PERFECTLY!!! Enjoy your weekends - buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
HALLELUIA!!! You're all geniuses - thank you SO much - it works
PERFECTLY!!! Enjoy your weekends - buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a val
Just so that you are aware, it is typically the ideal that ISNA is use
instead of ISERROR because ISERROR will return TRUE for even syntactical errors. For instance, =ISERROR(VLOOKUP(HJAHSDFJADHF)) will return TRUE. Best to replace ISERROR with ISNA. "buffgirl71" wrote: HALLELUIA!!! You're all geniuses - thank you SO much - it works PERFECTLY!!! Enjoy your weekends - buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine VLOOKUP and IF function so #NA isn't returned as a val
Thank you for your advice. I will make the change from ISERROR to ISNA
in my formula. ExcelChampion wrote: Just so that you are aware, it is typically the ideal that ISNA is use instead of ISERROR because ISERROR will return TRUE for even syntactical errors. For instance, =ISERROR(VLOOKUP(HJAHSDFJADHF)) will return TRUE. Best to replace ISERROR with ISNA. "buffgirl71" wrote: HALLELUIA!!! You're all geniuses - thank you SO much - it works PERFECTLY!!! Enjoy your weekends - buffgirl71 wrote: I am using VLOOKUP and when that function doesn't find the value in the table, it returns "#NA" as the value. I then can't sum the column because of the #NA in some of the cells. Is there a way that I can return a zero or a null instead of the #NA? I was thinking that perhaps the VLOOKUP could be combined with IF... Thanks for your help in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pastable function using VLOOKUP? | Excel Worksheet Functions | |||
| How do I combine If and VLookup function? | Excel Worksheet Functions | |||
| Help with VLookup function | Excel Worksheet Functions | |||
| Array Function with VLOOKUP | Excel Worksheet Functions | |||
| How to combine a vlookup with a sumif function!!! | Excel Discussion (Misc queries) | |||
Hybrid Mode
