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.

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>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.
>

=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.
>
>

=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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> >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.
> >

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> >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.
> >

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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > >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.
> > >
>
>

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> >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.
>> >
>

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.