# #N/A Error

T

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

J.

D

#### Dave Peterson

In xl2003 and before:

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

in xl2007:

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

M

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

T

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

A

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

T

#### Tokeroo

result remains #N/A

thanks for the quick response

T

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

M

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

M

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