Error suppressing with INDEX/MATCH

B

BKO

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
 
B

bj

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
 
S

ShaneDevenshire

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