# Avoid Second lookup - performance issue??

#### kwagner1

##### Active Member
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)))

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

bump

#### T. Valko

##### Well-known Member
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

##### MrExcel MVP
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

##### Active Member
thanks.... i will review the suggestions !

Replies
14
Views
115
Replies
3
Views
107
Replies
8
Views
44
Replies
1
Views
52
Replies
1
Views
90

1,109,465
Messages
5,528,962
Members
409,848
Latest member
Blomsten