Have Vlookup return a Value of 0 instead of #N/A
 

I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,"0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

One way:

=IF(ISNA(MATCH(F1,A1:A100,0)),0,VLOOKUP(F1,A1:C100 ,3,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Mr Mike" <Mr wrote in message
...
I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what
I'm
looking up), all calculations attached to that cell give me #N/A's as
well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,"0") in another cell that points to
my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place
the
vlookup function inside of the SUMIF.

Thanks for your help!

RagDyer, you area superhero. This is perfect. And thanks for exposing me to
IS functions! This will be new for me.

"RagDyer" wrote:

One way:

=IF(ISNA(MATCH(F1,A1:A100,0)),0,VLOOKUP(F1,A1:C100 ,3,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Mr Mike" <Mr wrote in message
...
I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what
I'm
looking up), all calculations attached to that cell give me #N/A's as
well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,"0") in another cell that points to
my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place
the
vlookup function inside of the SUMIF.

Thanks for your help!

Thanks for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Mr Mike" wrote in message
...
RagDyer, you area superhero. This is perfect. And thanks for exposing me
to
IS functions! This will be new for me.

"RagDyer" wrote:

One way:

=IF(ISNA(MATCH(F1,A1:A100,0)),0,VLOOKUP(F1,A1:C100 ,3,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Mr Mike" <Mr wrote in message
...
I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what
I'm
looking up), all calculations attached to that cell give me #N/A's as
well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,"0") in another cell that points
to
my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not
the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully
place
the
vlookup function inside of the SUMIF.

Thanks for your help!

Mr Mike wrote:
I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,"0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

Google vlookup #N/A

Alan Beban