# If error function in excel 2003

#### drewbizz

##### Board Regular
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

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

##### MrExcel MVP
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(...)))

##### MrExcel MVP

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
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

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
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
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
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]``

Replies
3
Views
152
Replies
4
Views
59
Replies
1
Views
228
Replies
3
Views
68
Replies
4
Views
101