short formula if answer should be 0 or blank

**jewellove** · 03-13-2013, 02:52 AM

Please modify the formula below so that the result is blank if the answer is 0 or null.

=iferror(index(sheet1!w:w;match(materlist!a1;index(sheet1!f:f;0);0));"")

I get a answer of - when there is no data in the cell..

Thank you.

**dilipandey** · 03-13-2013, 02:58 AM

Hi jewellove,

Try =iferror(if(index(sheet1!w:w;match(materlist!a1;index(sheet1!f:f;0);0))=0;"",;index(sheet1!w:w;match(materlist!a1;index(sheet1!f:f;0);0));"")

have'nt tested this on Excel but should be working at your end

Regards,
DILIPandey
<click on below * if this helps>

**:) Sixthsense :)** · 03-13-2013, 03:00 AM

Try this..

Formula:

Please Login or Register  to view this content.

**jewellove** · 03-13-2013, 03:04 AM

Hi, i tried incorporating your modification to my formula below:

=IFERROR(if(INDEX(Profile!W:W;MATCH(MASTERLIST!$G3;INDEX(Profile!$F:$F;0);0));"")=0;"";INDEX(Profile!W:W;MATCH(MASTERLIST!$G3;INDEX(Profile!$F:$F;0);0));"")

Excel can not process because too many arguments... Kindly correct. Thanks

**jewellove** · 03-13-2013, 03:22 AM

I tried sixthsense's modification.. but excel can't process it either...

Pls see print screen.

(Pivot return to blanks.jpg

**:) Sixthsense :)** · 03-13-2013, 03:26 AM

But my suggestion don't have Index function inside the match function

**dredwolf** · 03-13-2013, 04:04 AM

Also
dilipandey's:
=iferror(if(index(sheet1!w:w;match(materlist!a1;index(sheet1!f:f;0);0))=0;"",;index(sheet1!w:w;match(materlist!a1;index(sheet1!f:f;0);0));"")
is not the same as:
=IFERROR(if(INDEX(Profile!W:W;MATCH(MASTERLIST!$G3;INDEX(Profile!$F:$F;0);0));"")=0;"";INDEX(Profile!W:W;MATCH(MASTERLIST!$G3;INDEX(Profile!$F:$F;0);0));"")

not only are the references (slightly, which may be intentional) different, BUT, if you check the red portions of each, you have CHANGED the formula..( the blue highlighted comma looks like an edit error from changing to different region separators?)

Maybe this will give you a start ?

EDIT_
Also, neither solution said put the $ in front of the F's in 'F:F' parts, it's a whole column reference, making the $ redundant, now if you limited the range to F$1:F$100000, then it would be needed (AS shown)