Shortcut to ISERROR formula

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
=IF(COUNTIF(Q1EMPDATA,Sht16.EmployeeName),INDEX(Q1ALLData,MATCH(Sht16.EmployeeName,Q1EMPDATA,0),8),0)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top