Change formula so iferror works in 2003

[caritx]

Hi All,

I have this formula that works great in 2007 but I'm trying to save the file for 2003 and it wont work. Can someone please help me replace the iferror with something that will do the same get rid of my #n/a in this really long formula.


=IF(E2="Y","trib",IF(ISNUMBER(SEARCH("*holi09*",AI2)),"holi09",IFERROR(VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE)," ")))


Thanks!
Cari
<o></o>

 

[taigovinda]

Welcome to the board!

=iferror(expression," ")

is equivalent to (2003-friendly version):

=if(iserror(expression)," ",expression)

Hope that helps.

 

[Joe4]

Welcome to the Board!

Replace this part:
IFERROR(VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE)," ")
with
IF(ISNA(VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE))," ",VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE))

 

[xld]

Try

=IF(E2="Y","trib",
IF(ISNUMBER(SEARCH("*holi09*",AI2)),"holi09",
IF(ISNA(VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE))," ",
VLOOKUP(AI2,'Solicitation codes'!$A$2:$C$4000,2,FALSE))))

 

[Joe4]

Just a note on the previous responses.

Be very careful if you use ISERROR instead of ISNA. ISERROR will ignore a bunch of different errors, not just ISNA errors.
So if you have other errors in your data or formula, they may be ignored and can cause unexpected results. ISNA will only ignore the ISNA error.

 

[caritx]

Thank you all for all your help! I use this board a lot since I'm new and teaching myself formulas and vba.

 

[taigovinda]

Just a note on the previous responses.

Be very careful if you use ISERROR instead of ISNA. ISERROR will ignore a bunch of different errors, not just ISNA errors.
So if you have other errors in your data or formula, they may be ignored and can cause unexpected results. ISNA will only ignore the ISNA error.

Good point. I've definitely had this happen to me before, where iserror() caused me to miss a different error. Time to change my ways.