Avoid Second lookup - performance issue??

[kwagner1]

Greetings,

i'm using the following formula to place a "#n/a" in my cell whenever my INDEX result value result in a "0". I am charting and i do not want "0" values to get plotted - so I need to check if the INDEX formula results in a "0" and if it does THEN my result for the cell value is "#N/A" (not plotted on the chart) - other wise I return the number (non-zero).

Is there a better way to do this so I only perform 1 lookup; rather than a second when the result is <> 0?? (something like an "IS" function to check if the result = 0 - but i know that doesnt exist)

=IF(INDEX(($I$71:$I$80),MATCH($C5,$C$71:$C$80))=0,#N/A,INDEX(($I$71:$I$80),MATCH($C5,$C$71:$C$80)))

 

[kwagner1]

bump

 

[T. Valko]

Greetings,

i'm using the following formula to place a "#n/a" in my cell whenever my INDEX result value result in a "0". I am charting and i do not want "0" values to get plotted - so I need to check if the INDEX formula results in a "0" and if it does THEN my result for the cell value is "#N/A" (not plotted on the chart) - other wise I return the number (non-zero).

Is there a better way to do this so I only perform 1 lookup; rather than a second when the result is <> 0?? (something like an "IS" function to check if the result = 0 - but i know that doesnt exist)

=IF(INDEX(($I$71:$I$80),MATCH($C5,$C$71:$C$80))=0,#N/A,INDEX(($I$71:$I$80),MATCH($C5,$C$71:$C$80)))

Well, what makes it "complicated" is that you're returning 2 different data types depending on which condition is true.

Maybe put the INDEX formula in one cell then put a simple IF formula in another cell to test for 0.

A1: =INDEX($I$71:$I$80,MATCH($C5,$C$71:$C$80))
B1: =IF(A1=0,#N/A,A1)

 

[ZVI]

In Excel 2007+ for numeric I71:I80 try this:
=IFERROR(1/(1/INDEX(($I$71:$I$80),MATCH($C5,$C$71:$C$80,0))),NA())

 

[kwagner1]

thanks.... i will review the suggestions !