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


Thanks in Advance
J.
 
Ad

Advertisements

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
 
Ad

Advertisements

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

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top