I dont want #N/A!

KDD · External Since: Aug 28, 2005 Posts: 16

How do i use this formula to return 0 without using ISERROR.

=INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))

Problem is, if there is no value in I19, it returns #N/A, which in turn
effects all my other formulae linked to that cell into #N/A.

Pls help. Thank you.
--
KDDXB

Dave Peterson · External Since: Jul 08, 2005 Posts: 16047

Maybe just check for i19 first.

=if(i19="","",index(....))

I showed "", but you could use any thing you wanted.

KDD wrote:
>
> How do i use this formula to return 0 without using ISERROR.
>
> =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
>
> Problem is, if there is no value in I19, it returns #N/A, which in turn
> effects all my other formulae linked to that cell into #N/A.
>
> Pls help. Thank you.
> --
> KDDXB

--

Dave Peterson

KDD · External Since: Aug 28, 2005 Posts: 16

Hi Dave i dint understand your sugggstion

I19 is a dependant cell of J19, but i want to ensure K19 doesnt return #N/A
when J19=0.

pls help

--
KDDXB

"Dave Peterson" wrote:

> Maybe just check for i19 first.
>
> =if(i19="","",index(....))
>
> I showed "", but you could use any thing you wanted.
>
> KDD wrote:
> >
> > How do i use this formula to return 0 without using ISERROR.
> >
> > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> >
> > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > effects all my other formulae linked to that cell into #N/A.
> >
> > Pls help. Thank you.
> > --
> > KDDXB
>
> --
>
> Dave Peterson
>

KDD · External Since: Aug 28, 2005 Posts: 16

Thanks Guys - i got the solution:

This works::

=IF(J32=0,0,INDEX($D$6:$K$10,VLOOKUP(I32,$B$6:$C$10,2),HLOOKUP(J32,$D$4:$K$5,2)))

cheers and tx for your help. As always, thsi is the best place to come for
help on excel!
--
KDDXB

"KDD" wrote:

> Hi Dave i dint understand your sugggstion
>
> I19 is a dependant cell of J19, but i want to ensure K19 doesnt return #N/A
> when J19=0.
>
> pls help
>
>
>
> --
> KDDXB
>
>
> "Dave Peterson" wrote:
>
> > Maybe just check for i19 first.
> >
> > =if(i19="","",index(....))
> >
> > I showed "", but you could use any thing you wanted.
> >
> > KDD wrote:
> > >
> > > How do i use this formula to return 0 without using ISERROR.
> > >
> > > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> > >
> > > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > > effects all my other formulae linked to that cell into #N/A.
> > >
> > > Pls help. Thank you.
> > > --
> > > KDDXB
> >
> > --
> >
> > Dave Peterson
> >

Dave Peterson · External Since: Jul 08, 2005 Posts: 16047

You wrote:

Problem is, if there is no value in I19, it returns #N/A,

I checked to see what was in I19 first.

If you have to check I19 and J19, you could use Max's suggestion.

If i19 returns an error that you want to avoid:

=if(iserror(i19),"",....

KDD wrote:
>
> Hi Dave i dint understand your sugggstion
>
> I19 is a dependant cell of J19, but i want to ensure K19 doesnt return #N/A
> when J19=0.
>
> pls help
>
> --
> KDDXB
>
> "Dave Peterson" wrote:
>
> > Maybe just check for i19 first.
> >
> > =if(i19="","",index(....))
> >
> > I showed "", but you could use any thing you wanted.
> >
> > KDD wrote:
> > >
> > > How do i use this formula to return 0 without using ISERROR.
> > >
> > > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> > >
> > > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > > effects all my other formulae linked to that cell into #N/A.
> > >
> > > Pls help. Thank you.
> > > --
> > > KDDXB
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

CLR · External Since: Jan 09, 2005 Posts: 1055

Try something like replacing your VLOKUP section with........

=IF(I19<>0,YourVlookupFormula,0)

Vaya con dios,
Chuck, CABGx3

"KDD" <KDD DeleteThis @discussions.microsoft.com> wrote in message
news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
> How do i use this formula to return 0 without using ISERROR.
>
> =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
>
> Problem is, if there is no value in I19, it returns #N/A, which in turn
> effects all my other formulae linked to that cell into #N/A.
>
> Pls help. Thank you.
> --
> KDDXB

KDD · External Since: Aug 28, 2005 Posts: 16

CLR, there's no change in the formula result. Still showing #N/A.

Just a question: J19 is dependant on I19. I19 is also a formula driven
cell. Does that nullify the effect of your suggestion in my =index(....)
formula?
--
KDDXB

"CLR" wrote:

> Try something like replacing your VLOKUP section with........
>
> =IF(I19<>0,YourVlookupFormula,0)
>
> Vaya con dios,
> Chuck, CABGx3
>
>
> "KDD" <KDD DeleteThis @discussions.microsoft.com> wrote in message
> news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
> > How do i use this formula to return 0 without using ISERROR.
> >
> > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> >
> > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > effects all my other formulae linked to that cell into #N/A.
> >
> > Pls help. Thank you.
> > --
> > KDDXB
>
>
>

KL · External Since: Dec 27, 2004 Posts: 398

Hi KDD,

Just a wild guess: wouldn't the following formula do the trick without a
need for row [5 ]and column [C]:

=IF(J19=0,0,INDEX($D$6:$K$10,MATCH(I19,$B$6:$B$10), MATCH(J19,$D$4:$K$4)))

Regards,
KL

"KDD" <KDD DeleteThis @discussions.microsoft.com> wrote in message
news:3AF53663-6BD9-457A-9FF1-0164BA12D378@microsoft.com...
> Thanks Guys - i got the solution:
>
> This works::
>
> =IF(J32=0,0,INDEX($D$6:$K$10,VLOOKUP(I32,$B$6:$C$10,2),HLOOKUP(J32,$D$4:$K$5,2)))
>
> cheers and tx for your help. As always, thsi is the best place to come for
> help on excel!
> --
> KDDXB
>
>
> "KDD" wrote:
>
>> Hi Dave i dint understand your sugggstion
>>
>> I19 is a dependant cell of J19, but i want to ensure K19 doesnt return
>> #N/A
>> when J19=0.
>>
>> pls help
>>
>>
>>
>> --
>> KDDXB
>>
>>
>> "Dave Peterson" wrote:
>>
>> > Maybe just check for i19 first.
>> >
>> > =if(i19="","",index(....))
>> >
>> > I showed "", but you could use any thing you wanted.
>> >
>> > KDD wrote:
>> > >
>> > > How do i use this formula to return 0 without using ISERROR.
>> > >
>> > > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2),
>> > > HLOOKUP(J19,$D$4:$K$5,2))
>> > >
>> > > Problem is, if there is no value in I19, it returns #N/A, which in
>> > > turn
>> > > effects all my other formulae linked to that cell into #N/A.
>> > >
>> > > Pls help. Thank you.
>> > > --
>> > > KDDXB
>> >
>> > --
>> >
>> > Dave Peterson
>> >

Max · External Since: Sep 08, 2003 Posts: 3335

Try:

=IF(OR(I19="",J19=""),0,INDEX($D$6:$K$10,VLOOKUP(I19,$B$6:$C$10,2),HLOOKUP(J
19,$D$4:$K$5,2)))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"KDD" <KDD.TakeThisOut@discussions.microsoft.com> wrote in message
news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
> How do i use this formula to return 0 without using ISERROR.
>
> =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
>
> Problem is, if there is no value in I19, it returns #N/A, which in turn
> effects all my other formulae linked to that cell into #N/A.
>
> Pls help. Thank you.
> --
> KDDXB

KDD · External Since: Aug 28, 2005 Posts: 16

I tried this, but still not working. the cell returns #N/A. Can you suggest
an alternative pls?
--
KDDXB

"Max" wrote:

> Try:
>
> =IF(OR(I19="",J19=""),0,INDEX($D$6:$K$10,VLOOKUP(I19,$B$6:$C$10,2),HLOOKUP(J
> 19,$D$4:$K$5,2)))
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "KDD" <KDD.TakeThisOut@discussions.microsoft.com> wrote in message
> news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
> > How do i use this formula to return 0 without using ISERROR.
> >
> > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> >
> > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > effects all my other formulae linked to that cell into #N/A.
> >
> > Pls help. Thank you.
> > --
> > KDDXB
>
>
>

Max · External Since: Sep 08, 2003 Posts: 3335

"KDD" wrote
> I tried this, but still not working. the cell returns #N/A.
> Can you suggest an alternative pls?

The suggested error trap
> =IF(OR(I19="",J19=""),0, ...)

addressed your orig. post's line:
> > Problem is, if there is no value in I19, it returns #N/A

(there was an additional check for no value in J19 thrown in as well)

If you still get #N/A, that means it's coming from either the VLOOKUP or the
HLOOKUP (or both)

Try either:

=IF(OR(I19="",J19=""),0,IF(OR(ISNA(VLOOKUP(I19,$B$6:$C$10,2)),ISNA(HLOOKUP(J
19,$D$4:$K$5,2))),0,INDEX($D$6:$K$10,VLOOKUP(I19,$B$6:$C$10,2),HLOOKUP(J19,$
D$4:$K$5,2))))

or:

=IF(ISNA(INDEX($D$6:$K$10,VLOOKUP(I19,$B$6:$C$10,2),HLOOKUP(J19,$D$4:$K$5,2)
)),0,INDEX($D$6:$K$10,VLOOKUP(I19,$B$6:$C$10,2),HLOOKUP(J19,$D$4:$K$5,2)))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Trackeous · External Since: Nov 24, 2006 Posts: 7

Would this also take care of multiple if statements, such as:

=IF(B3<>"",B3, IF(B4<>"",B4,"",Etc.))

Thought I found one solution, but something doesn't work correctly.

"CLR" wrote:

> Try something like replacing your VLOKUP section with........
>
> =IF(I19<>0,YourVlookupFormula,0)
>
> Vaya con dios,
> Chuck, CABGx3
>
>
> "KDD" <KDD RemoveThis @discussions.microsoft.com> wrote in message
> news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
> > How do i use this formula to return 0 without using ISERROR.
> >
> > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
> >
> > Problem is, if there is no value in I19, it returns #N/A, which in turn
> > effects all my other formulae linked to that cell into #N/A.
> >
> > Pls help. Thank you.
> > --
> > KDDXB
>
>
>

Don Guillett · External Since: Jan 04, 2006 Posts: 2988

Look in the help index for OR & AND

--
Don Guillett
SalesAid Software
dguillett1.RemoveThis@austin.rr.com
"Trackeous" <Trackeous.RemoveThis@discussions.microsoft.com> wrote in message
news:0E3B9FCC-7C28-4136-B8CA-0A845071E0A0@microsoft.com...
> Would this also take care of multiple if statements, such as:
>
> =IF(B3<>"",B3, IF(B4<>"",B4,"",Etc.))
>
> Thought I found one solution, but something doesn't work correctly.
>
> "CLR" wrote:
>
>> Try something like replacing your VLOKUP section with........
>>
>> =IF(I19<>0,YourVlookupFormula,0)
>>
>> Vaya con dios,
>> Chuck, CABGx3
>>
>>
>> "KDD" <KDD.RemoveThis@discussions.microsoft.com> wrote in message
>> news:BCE856E3-2F80-4A61-8B5B-48343E0CAA01@microsoft.com...
>> > How do i use this formula to return 0 without using ISERROR.
>> >
>> > =INDEX($D$6:$K$10, VLOOKUP(I19,$B$6:$C$10,2), HLOOKUP(J19,$D$4:$K$5,2))
>> >
>> > Problem is, if there is no value in I19, it returns #N/A, which in turn
>> > effects all my other formulae linked to that cell into #N/A.
>> >
>> > Pls help. Thank you.
>> > --
>> > KDDXB
>>
>>
>>