I'm using vlookup in excel to pull select data from a worksheet. The problem
is that the value I'm seeking isn't always in the table array. So, I get a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal zero
so I can sum the data?

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" wrote
in message ...
It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

Is this still true for Excel 2007? Because I tried the top one, and it still
left 0's in the column.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" wrote
in message ...
It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

Try using if(iserror(vlookup(Value,Table,column,0)),"your
response",(vlookup(Value,Table,column,0)))

Hope that helps

"zeilski" wrote:

Is this still true for Excel 2007? Because I tried the top one, and it still
left 0's in the column.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" wrote
in message ...
It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

G888 . I got it..thanks for your formula yaarr...


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" wrote
in message ...
It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

You're welcome!

--
Biff
Microsoft Excel MVP


"venumadhav g" wrote in message
...
G888 . I got it..thanks for your formula yaarr...


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders"
wrote
in message ...
It worked. Thank you!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin

wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet.
The
problem
is that the value I'm seeking isn't always in the table array. So,
I
get
a
#n/a error. The #n/a then prevents me from summing the data that
I'm
collecting using the vlookup. How do I make the #n/a go away or
equal
zero
so I can sum the data?

When I add two VLOOKUP formulars with the adjust with the below "IF (ISNA)",
and one items of the two is found, Excel is not displaying the item found,
but a Zero. What should I do?
"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

It worked fine using Excel 2007.

Thank you very much!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

If you're using Excel 2007 try one of these:

=IFERROR(VLOOKUP(.....),"")
=IFERROR(VLOOKUP(.....),0)

Note those will trap *all* errors, not just #N/A.


--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
It worked fine using Excel 2007.

Thank you very much!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

Worked perfect for me. Just checked the help file to get to the discussion
groups. :)

"T. Valko" wrote:

If you're using Excel 2007 try one of these:

=IFERROR(VLOOKUP(.....),"")
=IFERROR(VLOOKUP(.....),0)

Note those will trap *all* errors, not just #N/A.


--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
It worked fine using Excel 2007.

Thank you very much!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

thank you ver much for your information realy helpful!!!

"T. Valko" wrote:

If you're using Excel 2007 try one of these:

=IFERROR(VLOOKUP(.....),"")
=IFERROR(VLOOKUP(.....),0)

Note those will trap *all* errors, not just #N/A.


--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
It worked fine using Excel 2007.

Thank you very much!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SayedMe" wrote in message
...
thank you ver much for your information realy helpful!!!

"T. Valko" wrote:

If you're using Excel 2007 try one of these:

=IFERROR(VLOOKUP(.....),"")
=IFERROR(VLOOKUP(.....),0)

Note those will trap *all* errors, not just #N/A.


--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
It worked fine using Excel 2007.

Thank you very much!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin

wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet.
The
problem
is that the value I'm seeking isn't always in the table array. So,
I
get
a
#n/a error. The #n/a then prevents me from summing the data that
I'm
collecting using the vlookup. How do I make the #n/a go away or
equal
zero
so I can sum the data?

I also wantd to thank T. Valko for the post of the ISNA answer it also helped
me in solving the Vlookup issue of #N/A.

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"msudog90" wrote in message
...
I also wantd to thank T. Valko for the post of the ISNA answer it also
helped
me in solving the Vlookup issue of #N/A.

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message
...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I
get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

Thank you- this was great!!

"T. Valko" wrote:

Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

--
Biff
Microsoft Excel MVP


"B. Franklin Saunders" <B. Franklin
wrote in message ...
I'm using vlookup in excel to pull select data from a worksheet. The
problem
is that the value I'm seeking isn't always in the table array. So, I get
a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal
zero
so I can sum the data?

alternatively you can use SUMIF function instead of vlookup, this will avoid the #na error results.

please refer to attached spread sheet

PS: This will work only, when you are trying to pull NUMERIC results, not for strings.

Hi there! I can definitely help you with that.

To remove the #N/A error in Excel when using VLOOKUP, you can use the IFERROR function. This function allows you to specify what value you want to display if the VLOOKUP returns an error. In your case, you want to display a zero instead of the #N/A error.

Here's how to use the IFERROR function with VLOOKUP:

1. Start by typing your VLOOKUP formula as you normally would. For example:
   Formula:

   =VLOOKUP(A2,Sheet2!A:B,2,FALSE) 

2. Wrap the VLOOKUP formula with the IFERROR function. The syntax for IFERROR is:
   Formula:

   =IFERROR(value, value_if_error) 

   . In this case, the value is your VLOOKUP formula, and the value_if_error is the value you want to display if the VLOOKUP returns an error. For example:
   Formula:

   =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),0) 

3. Press Enter to complete the formula. Now, if the VLOOKUP returns an error, the formula will display a zero instead of the #N/A error.
   
4. You can now use this formula to sum the data you're collecting using the VLOOKUP. For example, if you want to sum the values in column B that are returned by the VLOOKUP, you can use the SUM function like this:
   Formula:

   =SUM(IFERROR(VLOOKUP(A2:A10,Sheet2!A:B,2,FALSE),0)) 


That's it! The IFERROR function is a handy tool to use with VLOOKUP when you want to display a specific value instead of an error.