Why this match/Index formula works in Excel 2007 but not in Excel 2002??

[EvelynLu]

Hi all experts out there,

May I know why my below formula work for Excel 2007 but not Excel 2002?

=IFERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1),"")

As for Excel 2007, there is nothing display when nothing match. Whereas when open the Excel file in version 2002,
it appears "#Name" error. May I know how can I rectify this issues?

Thank you so much for all advise.

 

[easy2understandexcel]

IFERROR came out in 2007

You need to use IF(iserror()) for 2003 and earlier

 

[EvelynLu]

Thank you so much easy2understandexcel.

But if I use this formula If(iserror()) and run it in 2007, can it work too?

 

[easy2understandexcel]

Yes, it is a more cumbersome formula because you have to write out the whole formula for both the true and the false scenarios... (iferror allows you to write the formula only once)
Let me know if you need help with the context of if(iserror())

 

[EvelynLu]

Oh... I see.

May I know how should I convert my this formula to using if(isError())?

=IFERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1),"")

Thank you so much for your kind help.

 

[MARK858]

Try...

=IF(ISERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1)),"",INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1))

 

[EvelynLu]

Hi mark858,

I have try the coding and as usual it works in 2007 but not 2002... May I know why?

 

[MARK858]

I am afraid I can't at first glance see a reason why it wouldn't work in 2002, what is it returning?

 

[FDibbins]

This should work...
=IF(isERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1)),"",INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1))

 

[EvelynLu]

Another Question, understand that Excel 2002 can't use xltm format... May I know can I save my xltm file to xlt file?

Will there be any impact to my Xltm file?

Thank you.