#N/A Error

[Tokeroo]

formula in question
=IF($D$23="","",VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE))

my problem is D23 has another vlookup equation in that cell, so when it
doesn't display any information i get a #N/A. is there a symbol i can
use to tell excel that when it doesn't find a result but finds an
equation to print "0"

i.e.
=IF($D$23="FUNC","0",VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE))


Thanks in Advance
J.

 

[Dave Peterson]

In xl2003 and before:

=if($d$23="","",if(iserror(vlookup(...)),0,vlookup(...)))

in xl2007:

=if($d$23="","",iferror(vlookup(...),0))

 

[Max]

Try with an ISNA trap for D23, something like this:
=IF(ISNA($D$23),0,VLOOKUP($D$23,'Registration Page'!A1012:Y2010,21,FALSE))

 

[Tokeroo]

so inputed the formula:
=IF($D$23="","",IF(ISERROR(VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)),0,VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)))

it does blank the cell, but when D23 does show a result instead of the
formula, the cell remains blank

 

[Alan]

Wrap the VLOOKUP in ISNA,
=IF(ISNA(your vlookup formula),"Error Message or whatever",Your vlookup
formula)
eg
=IF(ISNA(VLOOKUP(E2,A1:B12,2,FALSE)),"",VLOOKUP(E2,A1:B12,2,FALSE))
Regards,
Alan

 

[Tokeroo]

result remains #N/A

thanks for the quick response

 

[Tokeroo]

so this finally worked, i figured i post this for you guys as well,
thanks for your time.

=IF(ISERROR(IF($D$23="","",VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE))),0,IF($D$23="","",VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)))

 

[Max]

Looks like you want the other vlookup trapped as well,
not just the one present in D23

Try:
=IF(ISNA($D$23),0,IF(ISNA(VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)),0,VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)))

 

[Max]

Try also this revised* version using ISNA (as posted in the other branch):
=IF(ISNA($D$23),0,IF(ISNA(VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)),0,VLOOKUP($D$23,'Registration
Page'!A1012:Y2010,21,FALSE)))

*its shorter than the one you posted below, and it uses the more appropriate
ISNA trap instead of ISERROR