ISNA Help Please Please please

[Mattlynn via OfficeKB.com]

Hi
Can someone please add an ISNA bit to this please for use into excel 2007.
I have been trying for ages and have been getting all tied up with it
Many Thanks
Matt



=VLOOKUP(J5,A:B,2,FALSE)

 

[Bob Phillips]

=IFERROR(VLOOKUP(J5,A:B,2,FALSE),"")

 

[Fred Smith]

Do it this way:
=IF(ISNA(VLOOKUP(J5,A:B,2,FALSE)),"",VLOOKUP(J5,A:B,2,FALSE))

Regards,
Fred

 

[Mattlynn via OfficeKB.com]

thank you everyone - thats just what i needed - perfectomundo

Matt

Do it this way:
=IF(ISNA(VLOOKUP(J5,A:B,2,FALSE)),"",VLOOKUP(J5,A:B,2,FALSE))

Regards,
Fred

Hi
Can someone please add an ISNA bit to this please for use into excel 2007.

[quoted text clipped - 3 lines]

=VLOOKUP(J5,A:B,2,FALSE)

 

[Mike H]

Mat,

I prefer this method

=IF(COUNTIF(A:A,J5)>0,VLOOKUP(J5,A:B,2,FALSE),"")

but if you want isna

=IF(ISNA(VLOOKUP(J5,A:B,2,FALSE)),"",VLOOKUP(J5,A:B,2,FALSE))

BTW. Unless it's essential then avoid using full columns, it can slow things
down a lot.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bob Phillips]

BTW. Unless it's essential then avoid using full columns, it can slow
things
down a lot.

In an arry formula using too many rows can be a problem Mike, but in a
non-array function, I don't think so.

 

[Fred Smith]

You're welcome. Thanks for the feedback.

Regards,
Fred

thank you everyone - thats just what i needed - perfectomundo

Matt

Do it this way:
=IF(ISNA(VLOOKUP(J5,A:B,2,FALSE)),"",VLOOKUP(J5,A:B,2,FALSE))

Regards,
Fred

Hi
Can someone please add an ISNA bit to this please for use into excel
2007.

[quoted text clipped - 3 lines]

=VLOOKUP(J5,A:B,2,FALSE)