Shortcut to ISERROR formula

[sujittalukde]

Currently I am using the following formula:
=INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8)

If no Match is found #N/A is coming

I want to put an error handler ISERROR formula. Like this:
=IF(ISERROR(INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8)),0,INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8))

I want to reduce the length of formula without repeating the main formula.

Is there any shortcit formula available so that if match is not found, result should be 0 and if found, the actual result should come.

Regards

 

[barry houdini]

Which version of Excel are you using? Are the results of the the INDEX formula numeric?

If you have Excel 2007 you can use IFERROR, i.e.

=IFERROR(INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8),0)

In older versions assuming the formula returns numbers try

=LOOKUP(9.9E+307,IF({1,0},0,INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8)))

 

[njimack]

=IF(COUNTIF(Q1EMPDATA,Sht16.EmployeeName),INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8),0)

 

[sujittalukde]

Thank you. Its working well.
regards