Formula compatability

Hi.

I have a problem with which i hope someone can help.

I have a spreadsheet created in Excel 2011, the issue I have is I need to use it on Excel 2003. In the spreadsheet i have the following formula:

=IF(ISBLANK($A3),"",IFERROR(INDEX(Temporary!$A$5:$N$752,MATCH(A3,Temporary!$A$5:$A$752,0),14),IFERROR(INDEX(Permanent!$A$5:$N$752,MATCH(A3,Permanent!$A$5:$A$752,0),14),"MFD")))

Which works perfectly as intended in 2011, but when i come to save it for 2003 I have to modify the formula slightly to:

=IF(ISBLANK($A3),"",IF(ISERROR(INDEX(Temporary!$A$5:$N$752,MATCH(A3,Temporary!$A$5:$A$752,0),14)),IF(ISERROR(INDEX(Permanent!$A$5:$N$752,MATCH(A3,Permanent!$A$5:$A$752,0),14)),"MFD")))

However, it won't work, I get a #NAME? error.

Can anyone point me in the right direction as to where I'm going wrong as Ive run out of ideas.

Thanks in advance.

Im not sure to be honest as i don't have access to Excel 2003 where I am.

example.xlsx

Many thanks

Sorry, I thought you wanted an example of the workbook, I am unable to post an example of the workbook in 2003 with the "If/Is error" amendment.

example.xls

Hopefully this is of use for you, I have saved the workbook with the 97-2004 Workbook format within Excel 2011.
Cells D3 and E3 have been changed to the IF/ISERROR amendment to the formula with the D4 and E4 cells with the original IFERROR formula. I can't get the amendment to work the same way In Excel 2003.

Originally Posted by joeu2004

I don't know why you get a #NAME? error; perhaps a typo. But you cannot simply replace the word IFERROR with ISERROR. The two functions behave very differently.

For backward-compatibility, the original formula should be redesigned as follows:

Please Login or Register  to view this content.

Note that the same formula works in Excel 2011 [sic] as well as Excel 2003, albeit less efficiently.

Caveat: I wonder if you should write $A3="" instead ISBLANK($A3). The difference is: ISBLANK is TRUE only if $A3 is empty; that is, no constant and no formula. $A3="" is TRUE in that case, too. But it is also TRUE if the value in $A3 is the null string, for example as a result of a formula like this one.

[EDIT] It is odd that you use $A3 in one reference, but A3 in another.

Thank you, that appears to have initially resolved the issue for me.