LOOKUP FUNCTION

[Anna]

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.

[Gary L Brown]

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

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
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.

[Kevin Vaughn]

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.

[Anna]

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
> gary_brown@ge_NOSPAM.com
> 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.