Hello,
I'm working in Excel 2000. I'm using the vector lookup function in my
current worksheet to find a specific item number in column A and the quantity
on order in column C in another worksheet. Everything is working fine except
that if the lookup can't find the item number and quantity I'm looking for,
it returns the highest quantity value it finds. How I do get the lookup to
return a 0 in the cell if the item number and quantity are not listed in the
external worksheet?

In summary, I want excel to tell me that it can't find the infomation that
I'm looking for and return a 0. Thank you for your help.

If(Isna(vlookup(......),0,vlookup(......))

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Anna" wrote:

Hello,
I'm working in Excel 2000. I'm using the vector lookup function in my
current worksheet to find a specific item number in column A and the quantity
on order in column C in another worksheet. Everything is working fine except
that if the lookup can't find the item number and quantity I'm looking for,
it returns the highest quantity value it finds. How I do get the lookup to
return a 0 in the cell if the item number and quantity are not listed in the
external worksheet?

In summary, I want excel to tell me that it can't find the infomation that
I'm looking for and return a 0. Thank you for your help.

If you are talking about vlookup which another poster has concluded, then use
his logic but also add the false argument; if it doesn't find an exact match,
it normally returns #n/a but the if(isna part tests for that and returns what
you put for the true part of the if test instead.
--
Kevin Vaughn


"Anna" wrote:

Hello,
I'm working in Excel 2000. I'm using the vector lookup function in my
current worksheet to find a specific item number in column A and the quantity
on order in column C in another worksheet. Everything is working fine except
that if the lookup can't find the item number and quantity I'm looking for,
it returns the highest quantity value it finds. How I do get the lookup to
return a 0 in the cell if the item number and quantity are not listed in the
external worksheet?

In summary, I want excel to tell me that it can't find the infomation that
I'm looking for and return a 0. Thank you for your help.

Ok, I'm not sure what I'm doing wrong. Here is my formula.
=LOOKUP(K10,'C:\Documents and Settings\All Users.HARTMANS\My
Documents\Anna''s Stuff\Breakdown sheet test\Excel Stuff\[Current
Backorders.xls]Backorder Rpt Summary ANNA'!$A:$A,'C:\Documents and
Settings\All Users.HARTMANS\My Documents\Anna''s Stuff\Breakdown sheet
test\Excel Stuff\[Current Backorders.xls]Backorder Rpt Summary
ANNA'!$C:$C)+IF(ISNA(K10))

Please let me know how I can change my formula to make it work. When I
enter variations of this formula the cell comes up blank, which is what I
want, but the cells that contain values that are correct are also turning
blank. If you need more information, please let me know.

Thank you for all your help!

"Gary L Brown" wrote:

If(Isna(vlookup(......),0,vlookup(......))

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Anna" wrote:

Hello,
I'm working in Excel 2000. I'm using the vector lookup function in my
current worksheet to find a specific item number in column A and the quantity
on order in column C in another worksheet. Everything is working fine except
that if the lookup can't find the item number and quantity I'm looking for,
it returns the highest quantity value it finds. How I do get the lookup to
return a 0 in the cell if the item number and quantity are not listed in the
external worksheet?

In summary, I want excel to tell me that it can't find the infomation that
I'm looking for and return a 0. Thank you for your help.