If error function in excel 2003

drewbny

Board Regular
Joined
Jan 16, 2011
Messages
98
Is there a way to create a iferror equivalent function in excel 2003? THis way I wouldnt have to create double vlookups to eliminate the N/A in my worksheets. Wouldnt this result in worksheets calculating faster?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I don't know if it is faster than the VLOOKUPs, but you could always try "IF(ISERROR("

=IF(ISERROR('your formula or reference here'),[value if true],[value if false])

Hope that helps.
 
Upvote 0
Is there a way to create a iferror equivalent function in excel 2003? THis way I wouldnt have to create double vlookups to eliminate the N/A in my worksheets. Wouldnt this result in worksheets calculating faster?

If a formula with VLOOKUP is expected, to return a number, try:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

This scheme can be used with any formula expression expected to return a number.

Note that this can be made to return a blank. However, the formula cell can be custom formatted as e.g.,

[=0]"";General

A similar scheme is possible with text-returning expression, including one with VLOOKUP like in:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"",VLOOKUP(...)))
 
Upvote 0
If a formula with VLOOKUP is expected, to return a number, try:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

This scheme can be used with any formula expression expected to return a number.

Note that this can be made to return a blank. However, the formula cell can be custom formatted as e.g.,

[=0]"";General

A similar scheme is possible with text-returning expression, including one with VLOOKUP like in:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"",VLOOKUP(...)))

The latter formula for text-returning expressions should read:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(...)))
 
Upvote 0
I need help converting my 2010 formula to a 2003 formula. I've tried to simply replace =IFERROR( with =If(ISERROR( but I get an error that says "too many arguments". What am I doing wrong?

=IFERROR(VLOOKUP(A:A,'Initial Measurement Period'!$A$2:$J$330,9,FALSE),"-")
 
Upvote 0
Try:
Code:
=if(iserror([COLOR=#333333]VLOOKUP(A:A,'Initial Measurement Period'!$A$2:$J$330,9,FALSE)),"-",[/COLOR]([COLOR=#333333]VLOOKUP(A:A,'Initial Measurement Period'!$A$2:$J$330,9,FALSE)[/COLOR][COLOR=#333333]))[/COLOR]
 
Upvote 0
If you're doing exact match vlookups (False as the 4th argument), And you can Sort the lookup data in Ascending order.
here's another option...

Typical example vlookup
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
with IFERROR
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"")

Try this as an equivalent.
With the data on Sheet2 sorted in ascending order by column A.

IF(LOOKUP(A2,Sheet2!A:A)=A2,LOOKUP(A2,Sheet2!A:A,Sheet2!B:B),"")

YES, 2 lookups is faster than 1 vlookup using FALSE as the 4th argument (YES, MUCH faster).
 
Last edited:
Upvote 0
This works, but I don't understand why. I have to update my entire spreadsheet and need to understand the reason for two vlookups.

Thanks!
 
Upvote 0
This works, but I don't understand why. I have to update my entire spreadsheet and need to understand the reason for two vlookups.

Thanks!

Try:
Code:
=if(iserror([COLOR=#333333]VLOOKUP(A:A,'Initial Measurement Period'!$A$2:$J$330,9,FALSE)),"-",[/COLOR]([COLOR=#333333]VLOOKUP(A:A,'Initial Measurement Period'!$A$2:$J$330,9,FALSE)[/COLOR][COLOR=#333333]))[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top