Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 98
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 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.



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 98
Default 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.




  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pastable function using VLOOKUP? zatomics Excel Worksheet Functions 1 May 23rd 06 06:17 AM
How do I combine If and VLookup function? Felicia Pickett Excel Worksheet Functions 2 December 14th 05 01:06 AM
Help with VLookup function JohnK Excel Worksheet Functions 6 August 22nd 05 12:52 PM
Array Function with VLOOKUP CoRrRan Excel Worksheet Functions 15 April 8th 05 05:54 PM
How to combine a vlookup with a sumif function!!! Mark the Shark Excel Discussion (Misc queries) 2 April 6th 05 11:54 AM


All times are GMT +1. The time now is 07:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"