| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
I dont want #N/A!
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 |
|
#2
|
|||
|
|||
|
Try:
=IF(OR(I19="",J19=""),0,INDEX($D$6:$K$10,VLOOKUP(I 19,$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" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
Try something like replacing your VLOKUP section with........
=IF(I19<0,YourVlookupFormula,0) Vaya con dios, Chuck, CABGx3 "KDD" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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(I 19,$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" wrote in message ... 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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
Thanks Guys - i got the solution:
This works:: =IF(J32=0,0,INDEX($D$6:$K$10,VLOOKUP(I32,$B$6:$C$1 0,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 |
|
#10
|
|||
|
|||
|
"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),HL OOKUP(J19,$D$4:$K$5,2))) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#11
|
|||
|
|||
|
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" wrote in message ... Thanks Guys - i got the solution: This works:: =IF(J32=0,0,INDEX($D$6:$K$10,VLOOKUP(I32,$B$6:$C$1 0,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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If and If Not
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" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If and If Not
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
