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

  • Thread starter Thread starter David Ryan
  • Start date Start date
D

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
 
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.
 
Thanks worked a treat

T. Valko said:
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.
Click to expand...
 
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
 
Back
Top