vlookup returning Errors (ISNA formula)

[Nora]

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

 

[Max]

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]

oops, errata, this:

.. a left/closing parens

should read as:

 

[ShaneDevenshire]

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