If error function in excel 2003

drewbizz

Board Regular
Joined
Jan 16, 2011
Messages
62
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?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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(...)))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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(...)))
 

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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),"-")
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533

ADVERTISEMENT

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]
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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!
 

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,114,650
Messages
5,549,183
Members
410,903
Latest member
natesreich
Top