I dont want #N/A!

K

KDD

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.
 
M

Max

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)))
 
C

CLR

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

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

Vaya con dios,
Chuck, CABGx3
 
D

Dave Peterson

Maybe just check for i19 first.

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

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

KDD

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

KDD

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
 
K

KDD

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?
 
D

Dave Peterson

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),"",....


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
Click to expand...
 
K

KDD

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 said:
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
Click to expand...
 
M

Max

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

The suggested error trap
=IF(OR(I19="",J19=""),0, ...)
Click to expand...

addressed your orig. post's line:
(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)))
 
K

KL

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
 
Top