Hi

When using the Vlookup statment on a cell where that cell content does not
exist in the vlookup range, how do you avoid the #NA or #value error?

I read previous posts and tried the ISNA formula but Excel will not accept
the formula- it stays at the change formula display. The formula I type is
below:

=IF(isna(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE),"No" ,VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0))

If anyone can help that would be much appreciated

Think you're just missing a left/closing parens for the ISNA(..) part:
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE)),"No ",VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nora" wrote:
When using the Vlookup statment on a cell where that cell content does not
exist in the vlookup range, how do you avoid the #NA or #value error?

I read previous posts and tried the ISNA formula but Excel will not accept
the formula- it stays at the change formula display. The formula I type is
below:

=IF(isna(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE),"No" ,VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0))

If anyone can help that would be much appreciated

oops, errata, this:
.. a left/closing parens

should read as:
.. a right/closing parens
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Nora,

You didn't state what version of Excel you were using, if it is 2007 you can
use a simplier formula:

=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE),"No" )

--
Cheers,
Shane Devenshire


"Nora" wrote:

Hi

When using the Vlookup statment on a cell where that cell content does not
exist in the vlookup range, how do you avoid the #NA or #value error?

I read previous posts and tried the ISNA formula but Excel will not accept
the formula- it stays at the change formula display. The formula I type is
below:

=IF(isna(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE),"No" ,VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0))

If anyone can help that would be much appreciated