Re: How do I remove the #div or N/A and make it 0 when using VLook

I understand all but the last section that begins with "table" Could you
tell me how this section should relate to my data?

"JulieD" wrote:

> Hi
>
> something along these lines?????
>
> =IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "JICDB" <JICDB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:38C1A72E-EA64-4296-AF77-4537B9385893@xxxxxxxxxxxxxxxx
> >I have a similair problem except that I need to add two lookup values. How
> > do I write this formula if one or the other could be either zero or non
> > existant - possibly even both. Let's assume 1 Vlookup value is Z and
> > another
> > is B. There are four possible situations and I probably need a nested if
> > stated (in the cell) but don't know how. Logically I want to say:
> >
> > If (Z=TRUE) AND (B=TRUE) THEN "0"
> > ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
> > ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
> > ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
> >
> > "CLR" wrote:
> >
> >> It works "as-is" on XL2k also.........
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >> "JulieD" <JulieD@xxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:#gRc$6DQFHA.1500@xxxxxxxxxxxxxxxxxxxxxxx
> >> > Hi
> >> > your formula should do exactly what you request and it works when i
> >> > test
> >> it
> >> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong
> >> > if
> >> > you click on the cell with the formula and use tools / formula auditing
> >> > /
> >> > evaluate formula to step through it?
> >> >
> >> > --
> >> > Cheers
> >> > JulieD
> >> > check out www.hcts.net.au/tipsandtricks.htm
> >> > ...well i'm working on it anyway
> >> > "JeaneIsaac" <JeaneIsaac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@xxxxxxxxxxxxxxxx
> >> > >I have a very large work*** that I am using the following vlook up
> >> > >on
> >> > >
> >> > >
> >> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
> >> ,Dataentry!$D$9:$P$1508,12,0))
> >> > > I am using the results of the lookup for another calculation, so I
> >> don't
> >> > > want the return value from my look up to be N/A. Can you please tell
> >> > > me
> >> > > how
> >> > > to make it 0 instead of the N/A when there is no match in the lookup
> >> > > ***.
> >> > > Thanks
> >> > >
> >> >
> >> >
> >>
> >>
> >>
>
>
>
.

Loading