Error suppressing with INDEX/MATCH
 

Hi, To prevent th nasty #NA in my sheet, I use

=IF(ERROR(MATCH( .... ));"";INDEX( .. ;MATCH( ...))

But now the aclculation time has increesed dramaticaly,

Is there another way to do this

Thanks a lot if anyone could help me with this

--
There are only 10 types of people in the world:
Those who understand binary and those who don't.

Error suppressing with INDEX/MATCH
 

to just not show th e#NA, one method is to use conditional formating to make
the font color white when an error occurs
<format<conditional formating
formula is =iserror(A1)
format-font-color-white

"BKO" wrote:

Hi, To prevent th nasty #NA in my sheet, I use

=IF(ERROR(MATCH( .... ));"";INDEX( .. ;MATCH( ...))

But now the aclculation time has increesed dramaticaly,

Is there another way to do this

Thanks a lot if anyone could help me with this

--
There are only 10 types of people in the world:
Those who understand binary and those who don't.

Error suppressing with INDEX/MATCH
 

Hi,

If you are only worried about this at print time and you are using Excel
2003, choose File, Page Setup, Sheet tab, set Cell errors as <blank.

If you are using 2007 replace the IF function with IFERROR. Using that
function you will only call the MATCH and INDEX functions once.

Minor changes - change ISERROR to ISNA and if you use the Conditional
Formatting approach change the conditional formatting formula to =ISNA(C5)
and set the font as stated to white.

--
Cheers,
Shane Devenshire


"BKO" wrote:

Hi, To prevent th nasty #NA in my sheet, I use

=IF(ERROR(MATCH( .... ));"";INDEX( .. ;MATCH( ...))

But now the aclculation time has increesed dramaticaly,

Is there another way to do this

Thanks a lot if anyone could help me with this

--
There are only 10 types of people in the world:
Those who understand binary and those who don't.