function =IFERROR LOOKUP works in excel 2007 not in excel 2003

[David Ryan]

Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without problems) the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}),0)

The latest compatability pack is installed.
Any advice appreciated

 

[T. Valko]

The IFERROR function is not compatible with Excel versions prior to Excel
2007. To reproduce your formulas functionality in prior versions you'd use
the combination of IF and ISERROR.

=IF(ISERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8})),0,LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}))

However, this makes the formula twice as long and when there is no error
condition the LOOKUP has to be performed twice.

=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}),0)

But, looking at your formula this isn't necessary. Basically, your formula
is conditioned on a single logical test. You want the same value returned if
the value of C9 is >=7 so you can reduce that formula to:

=IF(COUNT('HDU Roster'!C9),IF('HDU Roster'!C9>=7,8,0),0)

And it traps *any* errors that might be generated.

 

[David Ryan]

Thanks worked a treat

The IFERROR function is not compatible with Excel versions prior to Excel
2007. To reproduce your formulas functionality in prior versions you'd use
the combination of IF and ISERROR.

=IF(ISERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8})),0,LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}))

However, this makes the formula twice as long and when there is no error
condition the LOOKUP has to be performed twice.

=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8}),0)

But, looking at your formula this isn't necessary. Basically, your formula
is conditioned on a single logical test. You want the same value returned if
the value of C9 is >=7 so you can reduce that formula to:

=IF(COUNT('HDU Roster'!C9),IF('HDU Roster'!C9>=7,8,0),0)

And it traps *any* errors that might be generated.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Steve]

Here's a VB module I used in excel 2003

Go to Tools -> Macro ->-> Visual Basic Editor
Select insert -> Module
Paste this:

Function IfError(formula As Variant, show As String)
On Error GoTo ErrorHandler
If IsError(formula) Then
IfError = show
Else
IfError = formula
End If
Exit Function
ErrorHandler:
Resume Next
End Function

Then File Close and return to excel