#N/A Error

  • Thread starter Thread starter Tokeroo
  • Start date Start date
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.
 
In xl2003 and before:

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

in xl2007:

=if($d$23="","",iferror(vlookup(...),0))
 
Try with an ISNA trap for D23, something like this:
=IF(ISNA($D$23),0,VLOOKUP($D$23,'Registration Page'!A1012:Y2010,21,FALSE))
 
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
 
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
 
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)))
 
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)))
 
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
 
Back
Top