Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP

B

buffgirl71

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

Conan Kelly

Yes it can,

=IF(ISERROR{or other IS functions}(VLOOKUP([your vlookup arguments])),0,VLOOKUP([your vlookup arguments]))

I hope this helps,

Conan Kelly
 
D

Dave Peterson

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

buffgirl71

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 said:
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 said:
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.
 
B

buffgirl71

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 said:
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 said:
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.
 
B

buffgirl71

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

Guest

You have too many "=" remove them all except the first one.

buffgirl71 said:
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 said:
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 said:
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.
 
C

Conan Kelly

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 said:
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 said:
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 said:
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.
 
B

buffgirl71

HALLELUIA!!! You're all geniuses - thank you SO much - it works
PERFECTLY!!!

Enjoy your weekends -
 
B

buffgirl71

HALLELUIA!!! You're all geniuses - thank you SO much - it works
PERFECTLY!!!

Enjoy your weekends -
 
G

Guest

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

buffgirl71

Thank you for your advice. I will make the change from ISERROR to ISNA
in my formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Involved VLookup Function 6
removing #na from a sheet 7
VLOOKUP Issue??? 4
Combining "IF"statement with "Vlookup" 9
replacing a #na 2
Vlookup error? 6
Vlookup Help 5
Vlookup Match BUT Returning #NA 1

Top