Avoid Second lookup - performance issue??

kwagner1

Active Member
Joined
Jun 10, 2003
Messages
445
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)))
 

Some videos you may like

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
Joined
Apr 9, 2008
Messages
3,722
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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())
 

Watch MrExcel Video

Forum statistics

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