Replacing #N/A with a value

  • Thread starter Thread starter MAtt
  • Start date Start date
M

MAtt

I have a function that in some instances returns the #N/A
error. Is it possible for the value to be returned as 0
instead of #N/A? My function is

=INDEX(INDIRECT("'"&$B$1&"'!B:J"),MATCH(A3,INDIRECT
("'"&$B$1&"'!B:B"),0),8)

It is the MATCH portion of the arguement which causes the
#N/A on occasion.


Thanks for the Help!!
 
Hi
try
=IF(ISNA(MATCH(A3,INDIRECT("'"&$B$1&"'!B:B"),0)),0,INDEX(INDIRECT("'"&$
B$1&"'!B:J"),MATCH(A3,INDIRECT
("'"&$B$1&"'!B:B"),0),8))
 
One way

=IF(ISNA(MATCH(A3,INDIRECT("'"&$B$1&"'!B:B"),0)),0,whole formula)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Since INDIRECT already slows the performance, a 2-cell approach would be
more sensible:

Y2:

=MATCH(A3,INDIRECT("'"&$B$1&"'!B:B"),0)

X2:

=IF(ISNUMBER(Y2),INDEX(INDIRECT("'"&$B$1&"'!B:J"),Y2,8),0)

X2 is the result cell.
 
-----Original Message-----
I have a function that in some instances returns the #N/A
error. Is it possible for the value to be returned as 0
instead of #N/A? My function is

=INDEX(INDIRECT("'"&$B$1&"'!B:J"),MATCH(A3,INDIRECT
("'"&$B$1&"'!B:B"),0),8)

It is the MATCH portion of the arguement which causes the
#N/A on occasion.


Thanks for the Help!!
.
Click to expand...
I use a ISERROR nested inside an IF funtion to get rid of
these type of errors.

Try something like this:
=IF(ISERROR(your formula here),0,(your formula here))

The theory being IF "your formula here" returns an error
the result will be "0" and if no error is returned then
the cell will show the result of "your formula here".

You may have to monkey with the syntax some.
Good Luck
 
Back
Top